SQL Server 2019-使用传播操作员构建JSON

Question

我需要提供 JSON对象这样：

[
    {
        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,
    }
  ]

我很难构建 json 这样：（

使用此小提琴）

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

，但我不知道如何将 ...用户[1] ，而不是“ ID”：1 纳入用户 node：

有办法吗？

Answer 1

这实际上不是有效的JSON，但是您可以使用 String_agg 和 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

请注意，请注意您可能需要逃脱值，但是我不知道这是如何不 - JSON的工作是不能说的。