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, Mar 15 '18 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, Mar 15 '18 at 17:19

score 2 · Accepted Answer · answered Mar 18 '18 at 20:10

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

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

1 Answers1