Формирование json с вложенными объектами с помощью функции STUFF

Как я могу запросить связанные таблицы в json в процедуре tsql? Я пытаюсь использовать функцию STUFF. Мне нужно вывести такой json:

{
  "Id": "4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5",
  "tasks": [
  {
      "taskOrders": [
        {
          "merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
          "merchantOrderNumber": "1129"
        }
      ],
      "totalWeight": 3,
      "state": 1
    }
  ]
}

Я пытаюсь использовать такой запрос для раздела «задачи», и они объединяют его с верхними полями:

select '"tasks":[' + STUFF((
        select ',{
          "taskOrders": [
            '+STUFF((
                select ',{
                      "merchantId": "' + cast(o.merchantId as nvarchar(max)) + '",
                      "merchantOrderNumber": "' + cast(o.orderNumber as nvarchar(max)) + '"
                    }'
                from dbo.Order o
                where o.orderNumber = chk.orderNumber
                for xml path(''), type
            ).value('.', 'varchar(max)'), 1, 1, '')+'
          ],
          "totalWeight": ' + cast(chk.weight as nvarchar(max)) + ',
          "state": ' + cast(chk.state as nvarchar(max)) + '"
        }'
        from dbo.Checkpoints as chk
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') + ']'

я думаю, что я делаю что-то не так, так как вывод выглядит так:

"tasks":[{
"taskOrders": [     {
"merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
"merchantOrderNumber": "111"
 }
],
"totalWeight": 1,
"state": 1
 }
],
"totalWeight": 1,
"state": 1
}]

должен ли я использовать курсоры или это можно сделать с помощью STUFF?

Почему бы не использовать FOR JSON?

— 17.03.2022 12:44

Кроме того, STUFF() просто удаляет начальную запятую, это не имеет ничего общего с агрегацией строк. См. эта почта для получения дополнительной информации и FOR XML PATH и почему вы должны использовать STRING_AGG, если вы используете современную версию (если FOR JSON не работает).

— 17.03.2022 13:04

спасибо, не знал об этом

— 17.03.2022 13:59

sql sql-server tsql

17.03.2022 12:42

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

SQL Server 2016 и более поздние версии имеют встроенная поддержка JSON. Вместо использования STUFF и FOR XML попробуйте использовать вместо этого FOR JSON PATH, например:

/*
 * Example data...
 */
create table dbo.[Order] (
  merchantId uniqueidentifier,
  orderNumber varchar(10)
);
insert dbo.[Order] (merchantId, orderNumber) values
  ('EFEDE945-3A17-487B-919B-406C9DA8B8A9', '1129'),
  ('d79ce36c-5721-414b-a050-0a9105defe67', '1129');
  
create table dbo.Checkpoints (
  Id uniqueidentifier,
  orderNumber varchar(10),
  state int,
  weight int
);
insert dbo.Checkpoints (Id, orderNumber, state, weight) values
  ('4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5', '1129', 1, 1);

/*
 * Example query..
 */
select
  chk.Id,
  json_query((
    select
      o.merchantId,
      o.orderNumber as merchantOrderNumber
    from dbo.[Order] o
    where o.orderNumber = chk.orderNumber
    for json path
  ), '$') as tasks,
  weight as totalWeight,
  state
from dbo.Checkpoints chk
for json path, without_array_wrapper;

Что дает результат:

{
    "Id": "4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5",
    "tasks": [
        {
            "merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
            "merchantOrderNumber": "1129"
        },
        {
            "merchantId": "D79CE36C-5721-414B-A050-0A9105DEFE67",
            "merchantOrderNumber": "1129"
        }
    ],
    "totalWeight": 1,
    "state": 1
}

Просто гнида, STUFF() не выполняет здесь никакой реальной работы, она просто удаляет ведущую запятую. Технически это должно быть instead of using FOR XML PATH/

— 17.03.2022 13:05

17.03.2022 13:04