SQL Server OPENJSON читает вложенный json

У меня есть json, который я бы хотел проанализировать в SQL Server 2016. Существует иерархическая структура Projects- > Structures- > Properties. Я хотел бы написать запрос, который анализирует всю иерархию, но я не хочу указывать какие-либо элементы по номеру индекса, т.е. Я не хочу делать ничего подобного:

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 и Name возвращаются без проблем, но по какой-то причине я не могу получить вложенный json в "структурах". Вместо содержимого json он просто возвращает NULL:

Кто-нибудь знает, возможно ли это, и если да, то что я делаю неправильно?

Ответ 1

Если вы ссылаетесь на объект или массив JSON, вам нужно указать предложение AS JSON:

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

Смотрите FAQ: 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 (......)

Ответ 2

Использование 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

Ответ 3

Типовое! Я нашел ответ сразу после публикации вопроса. Вам нужно использовать ключевое слово "как json" при указании возвращаемых столбцов:

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

Ответ 4

Извините, что вскочил и возобновил старый поток, но есть ли способ включить путь к файлу, содержащему JSON?

Ответ 5

Это работает для 1 уровня, вложенного для меня. 2-й уровень, т.е. в приведенном выше примере Properties возвращает ошибку. Сообщение 258, уровень 15, состояние 1, строка 23 Невозможно вызвать методы для nvarchar (max).