Parse JSON Column containing multiple arrays-CodePudding

I'm trying to get this result but I'm not getting it. I've searched the forum but I can't find a solution to my problem. Can you help me and explain what is wrong with my query? thanks.

JSON

{
  "items": [
    {
      "id": 40054,
      "categories": [
        [
          28,
          168,
          53
        ]
      ]
    }
  ]
}

SQL

   Declare @JSON varchar(max)
    SELECT @JSON=BulkColumn
    FROM OPENROWSET (BULK 'C:\temp\test.json', SINGLE_CLOB) import
    INSERT INTO dbo.TABLE
    SELECT
        metadata.[id],
        categories.[categories1],
        categories.[categories2],
        categories.[categories3],
        getdate() as [dt_imp] FROM OPENJSON (@JSON)
    WITH(
        [items] NVARCHAR(MAX) AS JSON
        ) AS  Data
    
    OUTER APPLY OPENJSON([Data].[items])
    WITH(
        [id] NVARCHAR(MAX),
        [categories] NVARCHAR(MAX) AS JSON
        ) AS metadata
    
    OUTER APPLY OPENJSON([Metadata].[categories])
    WITH(   
        [categories1] NVARCHAR(MAX),
        [categories2] NVARCHAR(MAX),
        [categories3] NVARCHAR(MAX)
        ) AS categories

result I want

id	categories1	categories2	categories3
40054	28	168	53

CodePudding user response：

Perhaps this will help.

Updated to allow multiple arrays within the array.

Select ID   = json_value(A.value,'$.id')
      ,cat1 = json_value(B.value,'$[0]')
      ,cat2 = json_value(B.value,'$[1]')
      ,cat3 = json_value(B.value,'$[2]')
 From  OpenJSON(@JSON,'$.items') A
 Cross Apply OpenJSON(A.value,'$.categories') B

Results

ID      cat1    cat2    cat3
40054   28      168     53