Forming json with nested objects using STUFF function-CodePudding

How can I query related tables into json in tsql procedure? I'm trying to use STUFF function. I need to output such json:

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

am trying to use such query for "tasks" section and them concat it with upper fields:

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, '')   ']'

i guess i'm doing something wrong as the output looks like this:

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

should I use cursors or it's possible to accomplish it by STUFF?

CodePudding user response：

SQL Server 2016 and later have built-in support for JSON. Instead of using STUFF and FOR XML here try using FOR JSON PATH instead, e.g.:

/*
 * 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;

Which yields the output:

{
    "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
}