如何用SQL移除存储JSON字符串数组的列中匹配指定name的条目？

开发者阿泽

2026-7-23

需求与实现方案

表结构与数据

TableX（colX为nvarchar类型，存储逗号分隔的JSON字符串）

id           colX 
123          { "name": "abc", "type": "solid"}, { "name": "xyz", "type": "liquid"}
456          { "name": "xyz", "type": "liquid"}

TableY

id           colY
123          abc
456          xyz

需求

编写SQL查询，移除TableX.colX中与对应id的TableY.colY匹配name属性的JSON条目，预期输出：

id         Col
123        { "name": "xyz", "type": "liquid"}
456         NULL

推荐实现方法

你的思路完全可行，以下是具体落地步骤（以SQL Server为例）：

拆分JSON条目为多行：先把TableX里的逗号分隔JSON字符串转成标准JSON数组，再用OPENJSON拆分成单条JSON行
关联过滤保留条目：通过id关联TableY，筛选出name不匹配的JSON条目
聚合生成结果：把保留的单条JSON重新拼接成字符串，无保留条目则返回NULL

完整SQL代码

WITH SplitJson AS (
    SELECT 
        tx.id,
        JSON_QUERY(value) AS json_item
    FROM TableX tx
    CROSS APPLY OPENJSON('[' + REPLACE(tx.colX, '}, {', '},{') + ']')
    JOIN TableY ty ON tx.id = ty.id
    WHERE JSON_VALUE(value, '$.name') <> ty.colY
)
SELECT 
    id,
    CASE 
        WHEN COUNT(json_item) = 0 THEN NULL
        ELSE STRING_AGG(json_item, ', ') 
    END AS Col
FROM SplitJson
GROUP BY id;