Can't parsing an array of objects with Dynamic Keys using OPENJSON And SQL Server 2017

Question

I'm after running into some trouble parsing a set of JSON documents using SQL Server 2017.

I've encountered a nested array (sample below) within a document which uses dynamic ObjectId's i.e. 123 as the object Key as opposed to using a static key i.e. Category and then having a separate key:value to ref the ObjectId.

As a result I can't extract the items to a table using the regular CROSS APPLY OPENJSON syntax without specifying each individual Object Id (there are thousands)?

Is there a way to do this without referencing each ObjectId explicitly ideally i'd like to return all product items in a table and just have a field withe the categoryId.

 "ProductItems": {
            "123": [
              {
                "item": "13663"
              }
            ]
            "124": [
              {
                "value": "2336"
              },
              {
                "value": "3667"
              }             
            ],
            "453": [
              {
                "value": "8667"
              },
              {
                "value": "1956"
              }
            ]
          }

Shouldn't the brackets at the "ProductItems" level be square brackets since it is an array? — Michael
– Michael, Commented Mar 15, 2018 at 13:30
I double checked the source this reflects whats there .... It might have been more accurate if i stated that the ProductItems is an object that has a nested array of categories containing items — TheBeardedDBA
– TheBeardedDBA, Commented Mar 15, 2018 at 17:19

J Weezy · Accepted Answer · 2024-02-16 18:36:58Z

2

Try something like this:

DECLARE @x NVARCHAR(MAX)=
'{"ProductItems":{
"123": [
    {
    "item": "13663"
    }
],
"124": [
    {
    "value": "2336"
    },
    {
    "value": "3667"
    }             
],
"453": [
    {
    "value": "8667"
    },
    {
    "value": "1956"
    }
]
}}'

SELECT j2.*, j3.* 
FROM OPENJSON(@x) j1
CROSS APPLY OPENJSON(j1.Value) j2
CROSS APPLY OPENJSON(j2.Value) j3

edited Feb 16, 2024 at 18:36

J Weezy

4,0595 gold badges43 silver badges101 bronze badges

answered Mar 18, 2018 at 20:10

Razvan Socol

5,7492 gold badges22 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Can't parsing an array of objects with Dynamic Keys using OPENJSON And SQL Server 2017

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