Unmarshalling Hierarchical JSON in SQL Server 2016+

Question

We are attempting to parse out data from a JSON file with hierarchical data. Right now, I am just trying to extract the inner array's data. The basic structure of the document is:

[
 {
 "url": "https://www.google.com",
 "image-url": "https://www.google.com/imghp",
 "labels": [
 {
 "source": "Bob, Inc",
 "name": "Whips",
 "info": "Ouch"
 },
 {
 "source": "Weezles of Oregon",
 "name": "Chains",
 "info": "Let me go"
 },
 ...
 ],
 "Fact": "Fictional"
 },
 ...
] 

The final output required for this JSON object would be something like url, Fact, name, info (one row per label per object).

I can easily query the outer array of objects with OPENJSON() and JSON_VALUE(). However, I have not deduced how to extract the "labels" array so I can use OPENJSON on it to access the name and info fields. And that's where I need help.

If I can get the Labels array out, I can go from there. If I am missing something obvious, kick me an let me know what it is. But I've spent about five hours on this so far, and the solution isn't obvious (to me, at least)

• Laughing Vergil: Pre-emptively answering a likely question: We have no control over the format of the incoming JSON document.

Answer 1

In this case, I'd use a cross apply.

DECLARE @json NVARCHAR(MAX) = N'{"menu": {
 "header": "SVG Viewer",
 "items": [
 {"id": "Open"},
 {"id": "OpenNew", "label": "Open New"},
 null,
 {"id": "ZoomIn", "label": "Zoom In"},
 {"id": "ZoomOut", "label": "Zoom Out"},
 {"id": "OriginalView", "label": "Original View"},
 null,
 {"id": "Quality"},
 {"id": "Pause"},
 {"id": "Mute"},
 null,
 {"id": "Find", "label": "Find..."},
 {"id": "FindAgain", "label": "Find Again"},
 {"id": "Copy"},
 {"id": "CopyAgain", "label": "Copy Again"},
 {"id": "CopySVG", "label": "Copy SVG"},
 {"id": "ViewSVG", "label": "View SVG"},
 {"id": "ViewSource", "label": "View Source"},
 {"id": "SaveAs", "label": "Save As"},
 null,
 {"id": "Help"},
 {"id": "About", "label": "About Adobe CVG Viewer..."}
 ]
}}
'
 SELECT 
 JSON_VALUE(a.value, '$.header') AS MenuItem
 , JSON_VALUE(b.value, '$.id') AS Id
 , JSON_VALUE(b.value, '$.label') AS Label
 FROM OPENJSON(@json) a 
 CROSS APPLY OPENJSON(a.value, '$.items') b