如何用T-SQL在SQL Server生成含int数组的正确JSON以解决反序列化异常

需要生成如下结构的JSON，其中ItemIds为整数数组：

{
    "CategoryId": 100,
    "AccountId": 200,
    "ItemIds": [12, 13, 14, 15],
    "Created": "2024-10-23",
    "SiteId": 111,
    "PriorityLevel": 2
}

此前尝试的T-SQL写法生成的ItemIds是包含对象的字符串（如"{\"ItemId\":12},{\"ItemId\":13}"），导致C#反序列化int[]类型时抛出异常。

解决方案 #

使用JSON_QUERY包裹子查询的JSON结果，让SQL Server识别该内容为原生JSON而非普通字符串，从而直接嵌入外层JSON生成纯值数组：

WITH ItemList AS 
(
    SELECT 12 AS ItemId
    UNION ALL
    SELECT 13 AS ItemId
    UNION ALL
    SELECT 14 AS ItemId
    UNION ALL
    SELECT 15 AS ItemId
)
SELECT 
    100 AS CategoryId,
    200 AS AccountId,
    JSON_QUERY((SELECT ItemId FROM ItemList FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)) AS ItemIds,
    '2024-10-23' AS Created,
    111 AS SiteId,
    2 AS PriorityLevel
FOR 
    JSON PATH, WITHOUT_ARRAY_WRAPPER;

原理说明 #

• 子查询SELECT ItemId FROM ItemList FOR JSON PATH, WITHOUT_ARRAY_WRAPPER会生成类似{"ItemId":12},{"ItemId":13}的JSON片段，但默认会被外层FOR JSON当作字符串处理，自动添加转义符。
• JSON_QUERY函数告诉SQL Server：传入的内容是合法JSON，无需转义，直接将其作为嵌套JSON结构嵌入最终结果，最终生成[12,13,14,15]的纯值数组格式。

替代方案（适用于纯值列） #

如果ItemList仅包含纯值列，也可以结合STRING_AGG和JSON_ARRAY实现：

WITH ItemList AS 
(
    SELECT 12 AS ItemId
    UNION ALL
    SELECT 13 AS ItemId
    UNION ALL
    SELECT 14 AS ItemId
    UNION ALL
    SELECT 15 AS ItemId
)
SELECT 
    100 AS CategoryId,
    200 AS AccountId,
    JSON_ARRAY(STRING_AGG(ItemId, ',')) AS ItemIds,
    '2024-10-23' AS Created,
    111 AS SiteId,
    2 AS PriorityLevel
FOR 
    JSON PATH, WITHOUT_ARRAY_WRAPPER;

执行上述正确代码后，生成的JSON结构符合预期，可正常反序列化为C#中int[]类型的属性。

内容的提问来源于stack exchange，提问作者Stevey