SQL Server OPENJSON read nested json
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 (......)
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
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