Sql-server – Unmarshalling Hierarchical JSON in SQL Server 2016+

jsonsql serversql-server-2016

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)

Best Answer

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

Related Solutions

Sql-server – sql org chart hierarchical table to xml json

You can use the for xml path('') trick to concatenate the strings you need directly in the recursive function.

Something like this should do it for you:

create function dbo.SelectChild(@name as varchar(99)) returns varchar(max)
begin
return 
(
  select ',"children":[' + 
           stuff((
                 select ',{"name":"' + 
                          E.employee + '"'+
                          isnull(dbo.SelectChild(E.employee), '')+'}'
                 from dbo.emp_mgr as E
                 where E.manager = @name
                 for xml path(''), type
                 ).value('text()[1]', 'varchar(max)'), 1, 1, '')+']'
)
end

go

select '{"name":"' + E.employee + '"'+isnull(dbo.SelectChild(E.employee), '')+'}'
from dbo.emp_mgr as E
where E.manager is null

Result:

{  
   "name":"Adam",
   "children":[  
      {  
         "name":"Bob",
         "children":[  
            {  
               "name":"Charles",
               "children":[  
                  {  
                     "name":"Frank",
                     "children":[  
                        {  
                           "name":"Mike"
                        },
                        {  
                           "name":"Ned"
                        },
                        {  
                           "name":"Oliver"
                        },
                        {  
                           "name":"Peter"
                        }
                     ]
                  },
                  {  
                     "name":"George",
                     "children":[  
                        {  
                           "name":"Quinn"
                        },
                        {  
                           "name":"Robert"
                        },
                        {  
                           "name":"Scott"
                        },
                        {  
                           "name":"Ted"
                        },
                        {  
                           "name":"Urban"
                        }
                     ]
                  },
                  {  
                     "name":"Henry",
                     "children":[  
                        {  
                           "name":"Victor"
                        }
                     ]
                  }
               ]
            },
            {  
               "name":"David",
               "children":[  
                  {  
                     "name":"Ian",
                     "children":[  
                        {  
                           "name":"Wes"
                        }
                     ]
                  }
               ]
            },
            {  
               "name":"Ethan",
               "children":[  
                  {  
                     "name":"James",
                     "children":[  
                        {  
                           "name":"Xavier"
                        },
                        {  
                           "name":"Young"
                        },
                        {  
                           "name":"Zach"
                        }
                     ]
                  },
                  {  
                     "name":"Ken"
                  },
                  {  
                     "name":"Larry"
                  }
               ]
            }
         ]
      }
   ]
}

Sql-server – convert a VARCHAR field body to unquoted JSON result in SQL Server 2016 CTP 2.X

You'll need to wait for CTP 3 to use things like OPENJSON and JSON_VALUE. These constructs simply didn't ship in the CTPs up to and including CTP 2.4. That doesn't mean there isn't some way to get the result you want right now, but the amount of time and effort you might spend doing so is probably not worth it, especially if the functionality you need will be available in the next CTP, which should ship soon.

Related Question