Parse JSON Column containing multiple arrays

Question

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

It can have 1 to 3 elements. When I had 1 element, the categories2 and categories3 columns are NULL — Hugo Pereira
– Hugo Pereira, Commented Jan 31, 2023 at 0:24

John Cappelletti · Accepted Answer · 2023-01-31 00:28:21Z

3

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

edited Jan 31, 2023 at 0:28

answered Jan 31, 2023 at 0:23

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parse JSON Column containing multiple arrays

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related