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"
}
]
}
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