Search code examples
jsonparsinghierarchysql-server-2016open-json

SQL Server OPENJSON read nested json

I have some json that I would like to parse in SQL Server 2016. There is a hierarchy structure of Projects->Structures->Properties. I would like to write a query that parses the whole hierarchy but I don't want to specify any elements by index number ie I don't want to do anything like this:

openjson (@json, '$[0]')

or

openjson (@json, '$.structures[0]')

I had this idea that I could read the values of the top level project objects along with the json string that represents the structures below it, which could then be parsed separately. The problem is that the following code does not work:

declare @json nvarchar(max)
set @json = '
[
   {
      "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
      "Name":"Test Project",
      "structures":[
         {
            "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
            "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47",
            "Name":"Test Structure",
            "BaseStructure":"Base Structure",
            "DatabaseSchema":"dbo",
            "properties":[
               {
                  "IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 2",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               },
               {
                  "IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC",
                  "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F",
                  "Name":"Test Property 1",
                  "DataType":1,
                  "Precision":0,
                  "Scale":0,
                  "IsNullable":false,
                  "ObjectName":"Test Object",
                  "DefaultType":1,
                  "DefaultValue":""
               }
            ]
         }
      ]
   }
]';

select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max)
) as Projects

IdProject and Name get returned no problem but for some reason I cannot get the nested json held in 'structures'. Instead of the json content it just returns NULL:

enter image description here

Does anyone know if this is possible and if so, what am I doing wrong?

Solution

  • If you reference JSON object or array you need to specify AS JSON clause:

    select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) AS JSON
) as Projects

    See FAQ: https://learn.microsoft.com/en-us/sql/relational-databases/json/solve-common-issues-with-json-in-sql-server?view=sql-server-ver15#return-a-nested-json-sub-object-from-json-text-with-openjson

    If you want to apply OPENJSON on the returned structures array, you can use something like following code:

    select IdProject, Name, structures
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) AS JSON
) as Projects 
     CROSS APPLY OPENJSON (structures) WITH (......)