I have some json that I would like to parse in SQL Server 2016.  There is a hierarchy structure of Projects-&gt;Structures-&gt;Properties.  I would like to write a query that parses the whole hierarchy but I don&#39;t want to specify any elements by index number ie I don&#39;t want to do anything like this:

    openjson (@json, &#39;$[0]&#39;)

or

    openjson (@json, &#39;$.structures[0]&#39;)

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 = &#39;
    [
       {
          &quot;IdProject&quot;:&quot;97A76363-095D-4FAB-940E-9ED2722DBC47&quot;,
          &quot;Name&quot;:&quot;Test Project&quot;,
          &quot;structures&quot;:[
             {
                &quot;IdStructure&quot;:&quot;CB0466F9-662F-412B-956A-7D164B5D358F&quot;,
                &quot;IdProject&quot;:&quot;97A76363-095D-4FAB-940E-9ED2722DBC47&quot;,
                &quot;Name&quot;:&quot;Test Structure&quot;,
                &quot;BaseStructure&quot;:&quot;Base Structure&quot;,
                &quot;DatabaseSchema&quot;:&quot;dbo&quot;,
                &quot;properties&quot;:[
                   {
                      &quot;IdProperty&quot;:&quot;618DC40B-4D04-4BF8-B1E6-12E13DDE86F4&quot;,
                      &quot;IdStructure&quot;:&quot;CB0466F9-662F-412B-956A-7D164B5D358F&quot;,
                      &quot;Name&quot;:&quot;Test Property 2&quot;,
                      &quot;DataType&quot;:1,
                      &quot;Precision&quot;:0,
                      &quot;Scale&quot;:0,
                      &quot;IsNullable&quot;:false,
                      &quot;ObjectName&quot;:&quot;Test Object&quot;,
                      &quot;DefaultType&quot;:1,
                      &quot;DefaultValue&quot;:&quot;&quot;
                   },
                   {
                      &quot;IdProperty&quot;:&quot;FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC&quot;,
                      &quot;IdStructure&quot;:&quot;CB0466F9-662F-412B-956A-7D164B5D358F&quot;,
                      &quot;Name&quot;:&quot;Test Property 1&quot;,
                      &quot;DataType&quot;:1,
                      &quot;Precision&quot;:0,
                      &quot;Scale&quot;:0,
                      &quot;IsNullable&quot;:false,
                      &quot;ObjectName&quot;:&quot;Test Object&quot;,
                      &quot;DefaultType&quot;:1,
                      &quot;DefaultValue&quot;:&quot;&quot;
                   }
                ]
             }
          ]
       }
    ]&#39;;
    
    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 &#39;structures&#39;.  Instead of the json content it just returns NULL:

[![enter image description here][1]][1]

Does anyone know if this is possible and if so, what am I doing wrong?


  [1]: http://i.stack.imgur.com/kEE5S.png

All Open Json Solutions on Gang of Coders

Total of 1 Open Json Solutions

SQL Server OPENJSON read nested json