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)