Convert json into SQL table

Question

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

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

Answer 1

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;

Answer 2

Thanks a ton.
It works.