SQL Server OPENJSON read nested json

sql server json array
sql server json array length
how to store json data in sql server using c#
invalid object name 'openjson'.
sql openjson from table
sql server json recursive
json and sql server 2016
call json api from sql server

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?


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://msdn.microsoft.com/en-us/library/mt631706.aspx#Anchor_6

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 (......)

Reading JSON string with Nested array of elements, – OPENJSON() Table valued function: parses JSON text and returns rowset view of JSON. – JSON_Value() Scalar function: returns a value from  The OPENJSON () table value function transforms JSON object to one or many rows. It will not execute any command. It just returns a table row if JSON text is properly formatted. OPENJSON function will also work with JSON arrays and this function can also open nested/hierarchical JSON objects.


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

OPENJSON (Transact-SQL), Use OPENJSON to import JSON data into SQL Server, or to convert JSON data If you want to parse a sub-object from within jsonExpression, you can The following example returns a nested object by specifying the path:. The problem here is that your json is ill formatted - a proper json should contain pairs of key:value, that might be nested (meaning that the value will also contain json data). However, in your case - you have the session ids (20 and 30) as keys instead of as values - making it much harder to extract them from the json using the standard


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

SQL Server 2016, The OPENJSON () table value function transforms JSON object to one or many rows. It will not execute any command. It just returns a table row if JSON text is properly formatted. OPENJSON function will also work with JSON arrays and this function can also open nested/hierarchical JSON objects. OPENJSON seeks into the JSON text at the specified position and parses only the referenced fragment. For more info, see JSON Path Expressions (SQL Server). In SQL Server 2017 (14.x) and in Azure SQL Database, you can provide a variable as the value of path. The following example returns a nested object by specifying the path:


Microsoft SQL Server, We can parse root level properties using OPENJSON that will return Items array AS JSON fragment. Then we can apply OPENJSON again on Items array and  Over the years, Phil was struck by the problems of reading and writing JSON documents with SQL Server, and wrote several articles on ways of overcoming these problems. Now that SQL Server 2016 onwards has good JSON support, he thought that the articles would be forgotten. Not so, they continue to be popular, so he felt obliged to write about how you can use SQL Server's JSON support to speed


Consuming hierarchical JSON documents in SQL Server using , Now that SQL Server 2016 onwards has good JSON support, I was asked the other day how to use OpenJSON to parse JSON into a This Adjacency list is really the Database equivalent of any of the nested data structures  In the optional WITH clause, you specify a set of output columns, their types, and the paths of the JSON source properties for each output value. OPENJSON iterates through the array of JSON objects, reads the value on the specified path for each column, and converts the value to the specified type.


Shredding JSON Nested Arrays, Whilst reading up on SQL Server 2016 JSON functionality I have seen many examples of Reading examples from Itzik Ben-Gan's (b|t) book for 70-761 there are several examples of basic FROM OPENJSON(@json) OJC. If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions: ISJSON (Transact-SQL) tests whether a string contains valid JSON. JSON_VALUE (Transact-SQL) extracts a scalar value from a JSON string.