SQL Server OPENJSON读取嵌套的json

问题描述：

我有一些json，我想在SQL Server 2016中解析。有一个Projects-> Structures-> Properties的层次结构。我想编写解析整个层次的查询，但我不希望指定索引号的任何元素，即我不想做这样的事：SQL Server OPENJSON读取嵌套的json

openjson (@json, '$[0]')

或

openjson (@json, '$.structures[0]')

我有这个想法，我可以读取顶级项目对象的值以及表示它下面的结构的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和名称得到恢复没有问题，但由于某种原因，我不能在“结构”举行的嵌套JSON。取而代之的是JSON内容只是它返回NULL：

有谁知道这是可能的，如果是的话，我究竟做错了什么？

答

如果引用JSON对象或数组，你需要指定为JSON子句：

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

参见常见问题解答： https://msdn.microsoft.com/en-us/library/mt631706.aspx#Anchor_6

如果你想返回的结构数组上应用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 (......)

答

典型！我在发布问题后才找到答案。你需要指定的列时，使用“作为JSON”关键字返回：

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

答

使用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

SQL Server OPENJSON读取嵌套的json

相关推荐