SQL Server OPENJSON 读取嵌套的 json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37218254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server OPENJSON read nested json
提问by Slade
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:
我有一些想要在 SQL Server 2016 中解析的 json。有项目->结构->属性的层次结构。我想编写一个解析整个层次结构的查询，但我不想通过索引号指定任何元素，即我不想做这样的事情：
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:
我有一个想法，我可以读取顶级项目对象的值以及表示其下方结构的 json 字符串，然后可以单独解析这些值。问题是以下代码不起作用：
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:
IdProject 和 Name 没有问题返回，但由于某种原因，我无法获取保存在“结构”中的嵌套 json。它只返回 NULL 而不是 json 内容：
Does anyone know if this is possible and if so, what am I doing wrong?
有谁知道这是否可能，如果可能，我做错了什么？
回答by Ed.Schavelev
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
回答by Jovan MSFT
If you reference JSON object or array you need to specify AS JSON clause:
如果您引用 JSON 对象或数组，则需要指定 AS JSON 子句：
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
请参阅常见问题解答：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:
如果要在返回的结构数组上应用 OPENJSON，可以使用如下代码：
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 (......)
回答by Slade
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:
典型的！我在发布问题后找到了答案。指定要返回的列时，您需要使用 'as json' 关键字：
select IdProject, Name, structures
from openjson (@json)
with
(
IdProject uniqueidentifier,
Name nvarchar(100),
structures nvarchar(max) as json
) as Projects
