Convert json into SQL table

sourav dutta 211 Reputation points
2021-01-24T01:56:24.34+00:00

Dear Team,
How do I convert attached json into SQL table.

Thanks in advance. 59912-2020-12-14.txt

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,871 questions
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,201 Reputation points
    2021-01-24T03:47:36.52+00:00

    I saved the provided JSON file on the file system.
    Here is your solution.
    CROSS APPLY clause mimics 1-to-many relationship for nested JSON.

    SQL

    SELECT report.id, report.[Name], report.Active, report.TargetCampaignIds
        , ProductGroup.ProductGroupId, ProductGroup.MustSellThreshold, ProductGroup.[Sequence], ProductGroup.MustSellUOM
        , Product.*
    FROM OPENROWSET(BULK N'c:\Users\Yitzhak\Documents\SQL Server Management Studio\2020-12-14.json', SINGLE_CLOB) AS j
    CROSS APPLY
    OPENJSON(BulkColumn)
    WITH 
    (
        [id] varchar(100)                   '$.id'
        , [Name] varchar(100)               '$.Name'
        , [Active] BIT                      '$.Active'
        , [TargetCampaignIds] NVARCHAR(MAX) '$.TargetCampaignIds' AS JSON
        , [ProductGroups] NVARCHAR(MAX)     '$.ProductGroups' AS JSON   -- to use for nested JSON in CROSS APPLY
    ) AS report -- to use for nested JSON in CROSS APPLY
    CROSS APPLY OPENJSON(report.ProductGroups)
    WITH 
    (
        [ProductGroupId] varchar(100)   '$.ProductGroupId' 
        , [MustSellThreshold] INT       '$.MustSellThreshold' 
        , [Sequence] INT                '$.Sequence' 
        , [MustSellUOM] varchar(100)    '$.MustSellUOM' 
        , [ProductIds] NVARCHAR(MAX)    '$.ProductIds' AS JSON
    ) AS ProductGroup
    CROSS APPLY OPENJSON(ProductGroup.ProductIds)
    WITH
    (
        [ProductId] INT '$'
    ) AS Product;
    

1 additional answer

Sort by: Most helpful
  1. sourav dutta 211 Reputation points
    2021-01-25T13:17:54.26+00:00

    Thanks a ton.
    It works.

    0 comments No comments