如何用SQL的JSON_MODIFY将逗号分隔ItemID转为JSON数组？

问题描述 #

已创建Purchases表并插入数据，ItemID字段为逗号分隔的字符串。尝试使用JSON_MODIFY生成JSON时，得到的是{ "ItemID": "1,2" }这种字符串形式的结果，期望转为{ "ItemID": [ "1", "2" ] }的数组形式JSON，预期结果如下：

原SQL代码：

create table Purchases (ClientId int, ItemId varchar(128));
insert into Purchases (ClientId, ItemId)
values(1234, '1,2'),(5678, '3,4');

Declare @newtable table(
    clientid NVARCHAR(MAX),
    itemid NVARCHAR(MAX),
    json NVARCHAR(MAX)
);
insert into @newtable
select [ClientID], [ItemID],'{}'From Purchases;

Update @newtable
Set Json = JSON_MODIFY (Json, '$.ItemID', [ItemID]);

select * from @newtable;

解决方案 #

要将逗号分隔的字符串转为JSON数组，需先把字符串转换为合法的JSON数组格式，再通过JSON_QUERY让JSON_MODIFY识别为JSON类型（而非普通字符串）。修改后的SQL代码如下：

create table Purchases (ClientId int, ItemId varchar(128));
insert into Purchases (ClientId, ItemId)
values(1234, '1,2'),(5678, '3,4');

Declare @newtable table(
    clientid NVARCHAR(MAX),
    itemid NVARCHAR(MAX),
    json NVARCHAR(MAX)
);
insert into @newtable
select [ClientID], [ItemID],'{}'From Purchases;

Update @newtable
Set Json = JSON_MODIFY (
    Json, 
    '$.ItemID', 
    JSON_QUERY('["' + REPLACE(ItemID, ',', '","') + '"]')
);

select * from @newtable;

原理说明 #

1. 转换为JSON数组格式：使用REPLACE(ItemID, ',', '","')把逗号分隔的字符串1,2替换为1","2，再拼接前后的["和"]，得到["1","2"]这个合法的JSON数组字符串。
2. JSON_QUERY的作用：JSON_QUERY会返回被识别为JSON类型的值，这样JSON_MODIFY就不会把它当成普通字符串包裹引号，而是直接作为JSON数组插入到目标JSON中。

如果你的SQL Server版本为2017及以上（支持STRING_SPLIT和STRING_AGG），可以用更稳妥的方式构造数组（适配字符串含特殊字符的场景）：

Update @newtable
Set Json = JSON_MODIFY (
    Json, 
    '$.ItemID', 
    (
        SELECT JSON_QUERY('["' + STRING_AGG(QUOTENAME(value, '"'), ',') + '"]')
        FROM STRING_SPLIT(ItemID, ',')
    )
);

这种方式会先拆分字符串，再给每个元素添加引号并拼接成数组，避免特殊字符破坏JSON格式。

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