SQL Server 2019 - Build JSON with spread operator

Question

I'm required to supply a json object like this:

[
    {
        id: '59E59BC82852A1A5881C082D5FFCAC10',
        user: {
            ...users[1],
            last_message: "16-06-2022",
            topic: "Shipment"
        },
        unread: 2,
    },
    {
        id: '521A754B2BD028B13950CB08CDA49075',
        user: {
            ...users[2],
            last_message: "15-06-2022",
            topic: "Settings"
        },
        unread: 0,
    }
  ]

it is not difficult for me to build a json like this:

(with this fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bf62626de20d3ca7191aa9c1ef0cd39b)

[
   {
        "id": "59E59BC82852A1A5881C082D5FFCAC10",
        "user": {
            "id": 1,
            "last_message": "16-06-2022",
            "topic": "Shipment"
        },
        "unread": 2
   },
   {
        "id": "521A754B2BD028B13950CB08CDA49075",
        "user": {
            "id": 2,
            "last_message": "15-06-2022",
            "topic": "Settings"
        },
        "unread": 1
   },
   {
        "id": "898BB874D0CBBB1EFBBE56D2626DC847",
        "user": {
            "id": 3,
            "last_message": "18-06-2022",
            "topic": "Account"
        },
        "unread": 1
   }
]

but I have no idea how to put the ...users[1], instead of "id": 1 into user node:

is there a way?

Answer 1

This is not actually valid JSON, but you can create it yourself using STRING_AGG and CONCAT

SELECT
  '[' + STRING_AGG(u.spread, ',') + ']'
FROM (
    SELECT
      spread = CONCAT(
        '{id:''',
        u.userId,
        ''',user:{...users[',
        ROW_NUMBER() OVER (ORDER BY u.id),
        '],last_message: "',
        t.created_at,
        '",topic:"',
        t.topic,
        '"},unread:',
        (SELECT COUNT(*) FROM @tickets t3 WHERE t3.userId = u.userId AND t3.read_at IS NULL),
        '}'
      )
    FROM @Users u
    CROSS APPLY (
        SELECT top 1 
          t.ticketId,
          t.created_at,
          t.topic
        FROM @Tickets t
        WHERE t.userId = u.userId
        ORDER BY
          t.created_at DESC
    ) t
) u

Note that you may need to escape values, but I don't know how this not-JSON works so couldn't say.

db<>fiddle