基于关联表值移除SQL Server JSON数组中匹配元素

需求说明 #

当id与pax同时匹配时，需要从@T1表的JSON数组字段rooms中，移除所有存在于@T2表同id+pax组合下rooms字段中的元素。

测试数据 #

declare @T1 Table (id int ,pax int ,rooms nvarchar(512))
declare @T2 Table (id int ,pax int ,rooms nvarchar(512))

insert into @T1 (id,pax,rooms)
VALUES (1,3,'["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15","I13","I6-I5-I4-I3","B6","I36-I26-I16-I6"]'),
(1,4,'["I1-I11","I21-I31","I24-I34","B3-B5","B4-B6","I25-I35","I4-I5","I6-I16","I26-I36","I15-I25","I14-I24","I14-I4","I23-I31","I15-I5","I6-I16","I35-I25","I16-I6","I26-I36","B3-B5","B1-B3","I36-I26","I23-I13","B3","I3-I4"]'),
(2,5,'["I4-I5","I1-I11","I21-I31","B4-B6","I15-I25","I6-I16","I26-I36","I31-I23","I23-I13","I11-I21","I35-I25","B5-B3","I14-I4","I24-I34","B3-B1","I5-I15","B2-B4","I24-I14","I5-I3"]')

insert into @T2 (id,pax,rooms)
VALUES (1,3,'["I24-I34-I25-I35-I26-I36"]'), -- not exists
(1,3,'["I36-I26-I16-I6"]'),
(1,3,'["I6-I5-I4-I3"]'),
(1,3,'["I6-I16"]'), --not exists
(1,3,'["B6"]'),
(1,3,'["I13"]'),
(1,4,'["B1-B3"]'),
(1,4,'["B3"]'),
(1,4,'["I3-I4"]'),
(2,5,'["I5-I3"]')

预期结果示例 #

以@T1中id=1且pax=3的数据为例，移除指定元素后得到：

["I23","I31","I1","I5","I15","B2","I14","I4","I11","I3","I15","I5","I15","I15","I31","I31","I21","I35","B5-B3","I5-I15"]

用户尝试的代码 #

SELECT  t1.rooms,
        FilteredArray = JSON_QUERY(CONCAT('[', STRING_AGG(QUOTENAME(oj.Value,'"'), ','), ']'))
FROM    @T1 AS t1
        CROSS APPLY 
        (   SELECT  oj.Value
            FROM    OPENJSON(t1.rooms) AS oj
            join @T2 as t2 on t2.id=t1.id and t2.pax=t1.pax
            where t1.rooms<>t2.rooms
        ) AS oj
        group by t1.rooms

问题分析 #

这段代码的核心问题在于：

• 错误地将@T1的JSON元素与@T2的整行数据进行JOIN，导致每个@T2的行都会让@T1的元素重复一次，最终出现重复拼接的结果。
• 过滤条件t1.rooms<>t2.rooms是比较整个JSON字符串，而非判断@T1的元素是否存在于@T2的数组中，完全起不到元素过滤的作用。

正确解决方案 #

思路是先将@T2中所有需要移除的元素按id+pax分组收集，再从@T1的JSON元素中排除这些元素，最后重新拼接成JSON数组：

-- 先整理出每个id+pax下需要移除的所有元素
WITH T2_Rooms AS (
    SELECT 
        t2.id,
        t2.pax,
        oj.Value AS RemoveRoom
    FROM @T2 t2
    CROSS APPLY OPENJSON(t2.rooms) oj
)
SELECT 
    t1.id,
    t1.pax,
    OriginalRooms = t1.rooms,
    FilteredRooms = JSON_QUERY(CONCAT('[', STRING_AGG(QUOTENAME(oj.Value, '"'), ','), ']'))
FROM @T1 t1
CROSS APPLY OPENJSON(t1.rooms) oj
-- 排除需要移除的元素
LEFT JOIN T2_Rooms tr ON tr.id = t1.id AND tr.pax = t1.pax AND tr.RemoveRoom = oj.Value
WHERE tr.RemoveRoom IS NULL
GROUP BY t1.id, t1.pax, t1.rooms
ORDER BY t1.id, t1.pax;

代码说明 #

1. CTE T2_Rooms：将@T2中每个rooms数组拆分成单个元素，并关联对应的id和pax，得到所有需要移除的元素集合。
2. 主查询：
   • 拆分@T1的rooms数组为单个元素。
   • 通过LEFT JOIN关联T2_Rooms，过滤掉匹配到的需要移除的元素（即tr.RemoveRoom IS NULL的元素）。
   • 使用STRING_AGG将保留的元素重新拼接成JSON数组，并用JSON_QUERY确保结果被识别为JSON类型。

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