Search code examples
jsonsql-servert-sqlparsingopen-json

Parse JSON Column containing multiple arrays


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

Solution

  • 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