如何通过SQL Server JSON PATH动态创建多元素数组？

要生成包含多个元素的items数组，核心是在嵌套的子查询中通过UNION ALL构造多行数据——FOR JSON PATH会自动将子查询返回的多行结果转换为JSON数组的元素。

修改后的SQL查询 #

SELECT 
'##ORDER_ID##' AS 'order.id',  
'##ORDER_ACCOUNT##' AS 'order.account',
(
    SELECT '##ITEM_NUM_01##' AS 'partNumber', '##ITEM_UNIT_PRICE_01##' AS 'unitPrice'
    UNION ALL
    SELECT '##ITEM_NUM_02##' AS 'partNumber', '##ITEM_UNIT_PRICE_02##' AS 'unitPrice'
    -- 如需更多元素，继续添加UNION ALL和对应的SELECT语句即可
    FOR JSON PATH
) AS 'order.items'
FOR JSON PATH

生成的目标JSON结果 #

[{
    "order": {
        "id": "##ORDER_ID##",
        "account": "##ORDER_ACCOUNT##",
        "items": [{
                "partNumber": "##ITEM_NUM_01##",
                "unitPrice": "##ITEM_UNIT_PRICE_01##"
            },
            {
                "partNumber": "##ITEM_NUM_02##",
                "unitPrice": "##ITEM_UNIT_PRICE_02##"
            }
        ]
    }
}]

关键说明 #

你之前尝试UNION时可能忽略了两点：

1. 优先使用UNION ALL而非UNION——无需去重时，UNION ALL性能更好，且不会过滤掉结构一致的占位符行；
2. 子查询必须返回多行数据，FOR JSON PATH才会将其转为多元素数组——之前的子查询仅返回单行，所以数组只有一个元素。

内容的提问来源于Stack Exchange，提问作者fbauer65