SQL Server OPENJSON read nested json

Question

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:

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

Answer 1

Using CROSS APPLY:

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
    Projects.IdProject, Projects.Name as NameProject,
    Structures.IdStructure, Structures.Name as NameStructure, Structures.BaseStructure, Structures.DatabaseSchema,
    Properties.*    
from   openjson (@json)
with
(
    IdProject uniqueidentifier,
    Name nvarchar(100),
    structures nvarchar(max) as json
)
as Projects
cross apply openjson (Projects.structures)
with
(
    IdStructure uniqueidentifier,
    Name nvarchar(100),
    BaseStructure nvarchar(100),
    DatabaseSchema sysname,
    properties nvarchar(max) as json
) as Structures
cross apply openjson (Structures.properties)
with
(
    IdProperty uniqueidentifier,
    NamePreoperty nvarchar(100) '$.Name',
    DataType int,
    [Precision] int,
    [Scale] int,
    IsNullable bit,
    ObjectName nvarchar(100),
    DefaultType int,
    DefaultValue nvarchar(100)
)
as Properties

Answer 2

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://docs.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 (......)

Answer 3

Typical! I found the answer just after posting the question. You need to use the 'as json' key word when specifying the columns to return:

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