SQL Server FOR JSON Вложенный массив

Question

Мы пытаемся использовать FOR JSON Path в SQL Server 2016 для формирования вложенного массива из SQL-запроса.

SQL-запрос:

SELECT A, 
B.name as [child.name],
B.date as [child.date]
 from Table 1 join Table 2 on Table 1.ID=Table 2.ID FOR JSON PATH

Желаемый результат:

[{
A:"text",
   "child:"[
         {"name":"value", "date":"value"},
         {"name":"value", "date":"value"}

       ]
}]

Однако то, что мы получаем:

 [{
    A:"text",
    "child:" {"name":"value", "date":"value"}
  },
{
   A:"text",
  "child":{"name":"value", "date":"value"}
}]

Как мы можем использовать FOR JSON PATH для формирования вложенного дочернего массива.

Answer 1

Вместо соединения используйте вложенный запрос, например:

SELECT A, child=(SELECT
  B.name as [child.name],
  B.date as [child.date] from Table 2
where Table 2.ID=Table 1.ID FOR JSON PATH)
from Table 1 FOR JSON PATH

(запрос в вопросе разбит, так что этот запрос такой же неработающий, но должен дать вам идею)

Answer 2

Предполагая эту схему:

      create table parent(id int primary key, name varchar(100));
create table child(id int primary key, name varchar(100), parent_id int references parent(id));

Вот рабочее решение - abeit более запутанное - которое не включает коррелированные подзапросы и использует только FOR JSON PATH:

      SELECT
  parent.name AS [name],
  child.json_agg AS [children]
FROM parent
JOIN (
  SELECT
    child.parent_code,
    JSON_QUERY(CONCAT('[', STRING_AGG(child.json, ','), ']')) AS json_agg
  FROM (
    SELECT
      child.parent_code,
      (SELECT
        child.name AS [name]
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      ) AS json
    FROM child
  ) AS child
  GROUP BY child.parent_code
) AS child
ON child.parent_code = parent.code
FOR JSON PATH

Если у вас есть указатель на child.parent_id, затем используя коррелированный подзапрос, как предлагается, или эквивалент с CROSS/OUTER APPLY может быть более эффективным:

      SELECT
  parent.name AS [name],
  child.json AS [children]
FROM parent
OUTER APPLY (
  SELECT
    name AS [name]
  FROM child
  WHERE child.parent_id = parent.id
  FOR JSON PATH
) child(json)
FOR JSON PATH

Оба запроса вернут:

      [
    {
        "name": "foo",
        "children": [
            { "name": "bar" },
            { "name": "baz" }
        ]
    }
]

Answer 3

Это должно дать желаемый результат. Подзапросы, безусловно, лучший вариант. Попытка использовать соединения просто добавляет сложности, в которой нет необходимости.

      DECLARE @parents TABLE (ParentID int, [Name] nvarchar(max))
DECLARE @children TABLE (ChildID int, ParentID int, [Name] nvarchar(max), BirthDate datetime)

INSERT @parents (ParentID, [Name]) VALUES (1, 'Bob')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,1, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,1, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,1, 'Sam','20-JAN-2009')
INSERT @parents (ParentID, [Name]) VALUES (2, 'Joe')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,2, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,2, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,2, 'Sam','20-JAN-2009')
INSERT @parents (ParentID, [Name]) VALUES (3, 'Sarah')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (1,3, 'Billy','4-JUL-2000')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (2,3, 'Joan','19-SEP-2005')
INSERT @children (ChildID, ParentID, [Name], BirthDate) VALUES (3,3, 'Sam','20-JAN-2009')

SELECT 
    A = [Name]
    ,child =JSON_QUERY((
           SELECT [name] = [Name],
                [date] = BirthDate
           FROM @children c
           WHERE c.ParentID = p.ParentID
           FOR JSON PATH
       ))
FROM 
    @parents p
FOR JSON PATH

Я создаю скалярные функции, которые генерируют JSON для подзапросов. Например (предположим, что табличные переменные на самом деле являются таблицами):

      CREATE FUNCTION dbo.ChildrenJSON(@ParentID int)
RETURNS nvarchar(max)
BEGIN
    RETURN (
           SELECT [name] = [Name],
                [date] = BirthDate
           FROM @children c
           WHERE c.ParentID = @ParentID
           FOR JSON PATH
       )

END

Тогда ваш запрос может выглядеть так:

      SELECT 
    A = [Name]
    ,child =JSON_QUERY(dbo.ChildrenJSON(ParentID))
FROM 
   @parents 
FOR JSON PATH

Это чисто и легко читается.

Это результат:

      [
  {
    "A": "Bob",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  },
  {
    "A": "Joe",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  },
  {
    "A": "Sarah",
    "child": [
      {
        "name": "Billy",
        "date": "2000-07-04T00:00:00"
      },
      {
        "name": "Joan",
        "date": "2005-09-19T00:00:00"
      },
      {
        "name": "Sam",
        "date": "2009-01-20T00:00:00"
      }
    ]
  }
]