SQL Server如何将逗号分隔ID存储为JSON并实现增删特定值？

嘿，这个需求我之前帮不少开发者处理过，SQL Server其实有一套挺顺手的内置函数能搞定逗号分隔ID转JSON，以及后续的插入/删除操作，咱们一步步来拆解：

假设你的表名为your_table，主键是id，存储逗号分隔ID的列是comma_separated_ids，我们可以用STRING_SPLIT拆分字符串，再通过FOR JSON PATH生成标准的JSON数组：

-- 转换为数字类型的JSON数组（推荐，因为ID通常是数字）
UPDATE your_table
SET json_ids = (
    SELECT CAST(value AS INT) AS [*]
    FROM STRING_SPLIT(comma_separated_ids, ',')
    WHERE value <> '' -- 过滤空值
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE comma_separated_ids IS NOT NULL AND comma_separated_ids != '';

-- 如果需要字符串类型的数组，去掉CAST即可
UPDATE your_table
SET json_ids = (
    SELECT value AS [*]
    FROM STRING_SPLIT(comma_separated_ids, ',')
    WHERE value <> ''
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
)
WHERE comma_separated_ids IS NOT NULL AND comma_separated_ids != '';

执行后，原来的'1,2,3,4'会变成[1,2,3,4]（数字数组）或者["1","2","3","4"]（字符串数组），记得把json_ids列设为NVARCHAR(MAX)类型，确保能容纳足够长的JSON内容。

用JSON_MODIFY函数可以直接向数组追加元素，同时要先检查值是否已存在，避免重复插入：

-- 插入数字ID=5到指定行（假设主键id=1）
UPDATE your_table
SET json_ids = JSON_MODIFY(
    json_ids,
    'append $',
    5
)
WHERE id = 1
AND NOT EXISTS (
    SELECT 1
    FROM OPENJSON(json_ids)
    WHERE CAST(value AS INT) = 5 -- 匹配数字类型
);

-- 如果是字符串数组，把CAST去掉即可
UPDATE your_table
SET json_ids = JSON_MODIFY(
    json_ids,
    'append $',
    '5'
)
WHERE id = 1
AND NOT EXISTS (
    SELECT 1
    FROM OPENJSON(json_ids)
    WHERE value = '5'
);

'append $'表示向JSON数组的末尾追加元素，NOT EXISTS子句确保不会插入重复值。

删除需要先把JSON数组拆成临时表，过滤掉要删除的值，再重新生成JSON数组：

-- 删除数字ID=3（主键id=1）
UPDATE your_table
SET json_ids = ISNULL(
    (
        SELECT CAST(value AS INT) AS [*]
        FROM OPENJSON(json_ids)
        WHERE CAST(value AS INT) != 3
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ),
    '[]' -- 如果删除后数组为空，设为空数组而非NULL
)
WHERE id = 1
AND EXISTS (
    SELECT 1
    FROM OPENJSON(json_ids)
    WHERE CAST(value AS INT) = 3 -- 确保要删除的值存在
);

-- 字符串数组的删除逻辑
UPDATE your_table
SET json_ids = ISNULL(
    (
        SELECT value AS [*]
        FROM OPENJSON(json_ids)
        WHERE value != '3'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ),
    '[]'
)
WHERE id = 1
AND EXISTS (
    SELECT 1
    FROM OPENJSON(json_ids)
    WHERE value = '3'
);

用ISNULL处理删除后数组为空的情况，避免出现NULL值，保持JSON格式的一致性。

• 可以把插入、删除的逻辑封装成存储过程，方便重复调用，比如：

CREATE PROCEDURE InsertIdToJson
    @tableId INT,
    @targetId INT
AS
BEGIN
    UPDATE your_table
    SET json_ids = JSON_MODIFY(
        json_ids,
        'append $',
        @targetId
    )
    WHERE id = @tableId
    AND NOT EXISTS (
        SELECT 1
        FROM OPENJSON(json_ids)
        WHERE CAST(value AS INT) = @targetId
    );
END;

• 如果你的SQL Server版本低于2016，STRING_SPLIT和OPENJSON可能不支持，需要用自定义函数来拆分字符串，但2016及以上版本完全兼容上述操作。

内容的提问来源于stack exchange，提问作者Md. Parvez Alam