0

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)

  • Pre-emptively answering a likely question: We have no control over the format of the incoming JSON document. – Laughing Vergil Nov 8 '17 at 23:09
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.