如何改进SQL中JSON数组元素插入JSON对象的实现，规避字符串函数？

需求是向JSON数组的每个元素中插入指定的JSON对象，原方案依赖STRING_AGG和CONCAT等字符串函数重组数组，我们可以通过SQL Server的原生JSON功能优化，完全避免字符串操作。

优化后的代码 #

DECLARE @ArrayJSON  NVARCHAR(MAX) = '[{"BookName":"First Book", "Order": 1},{"BookName":"Second Book", "Order": 2}]';
DECLARE @ObjectJSON NVARCHAR(MAX) = '{"Page":1,"Chapter":2}';
DECLARE @FinalJSON  NVARCHAR(MAX);

WITH [ParsedOriginal] AS (
    SELECT 
        JSON_MODIFY([value], '$.Params', JSON_QUERY(@ObjectJSON)) AS []
    FROM OPENJSON(@ArrayJSON)
)
SELECT @FinalJSON = (SELECT * FROM [ParsedOriginal] FOR JSON PATH);

PRINT @@VERSION;
PRINT CONCAT('@ArrayJSON  = ', @ArrayJSON);
PRINT CONCAT('@ObjectJSON = ', @ObjectJSON);
PRINT CONCAT('@FinalJSON  = ', @FinalJSON);

方案说明 #

1. 解析原数组：通过OPENJSON将输入的JSON数组拆分为单个JSON元素
2. 修改元素：使用JSON_MODIFY为每个元素添加Params属性，JSON_QUERY用于确保插入的JSON对象不会被转义为字符串
3. 重组数组：通过指定空列名的方式，让FOR JSON PATH直接将所有处理后的JSON元素组合成标准JSON数组，无需手动拼接括号和逗号

执行结果 #

Microsoft SQL Server 2019

@ArrayJSON  = [{"BookName":"First Book", "Order": 1},{"BookName":"Second Book", "Order": 2}]
@ObjectJSON = {"Page":1,"Chapter":2}
@FinalJSON  = [{"BookName":"First Book","Order":1,"Params":{"Page":1,"Chapter":2}},{"BookName":"Second Book","Order":2,"Params":{"Page":1,"Chapter":2}}]

更高版本简化写法（SQL Server 2022+） #

如果使用SQL Server 2022及以上版本，还可以直接使用JSON_ARRAYAGG函数实现更简洁的写法：

DECLARE @ArrayJSON  NVARCHAR(MAX) = '[{"BookName":"First Book", "Order": 1},{"BookName":"Second Book", "Order": 2}]';
DECLARE @ObjectJSON NVARCHAR(MAX) = '{"Page":1,"Chapter":2}';
DECLARE @FinalJSON  NVARCHAR(MAX);

SELECT @FinalJSON = JSON_ARRAYAGG(JSON_MODIFY([value], '$.Params', JSON_QUERY(@ObjectJSON)))
FROM OPENJSON(@ArrayJSON);

PRINT @FinalJSON;

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