如何在MS SQL存储过程中从JSON字符串排除指定多值

原始JSON #

{"Country": {"Layer4": [{"ItemName": "Cabinet MT","ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName": "Other MT","ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName": "Cold MT","ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName": "Cold MT","ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"},{"ItemName": "Cabinet MT","ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID": "b4283692-7c14-46da-9480-9a2976187316"}

需求 #

移除Layer4数组中满足**ItemName = 'Cabinet MT' 或 ItemName = 'Other MT' 且 ItemId = 'cc3b0435-9ff5-4fd8-9f49-e049919a1414'**的元素，预期结果：

{"Country": {"Layer4": [{"ItemName": "Cold MT","ItemId": "cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName": "Cold MT","ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"},{"ItemName": "Cabinet MT","ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID": "b4283692-7c14-46da-9480-9a2976187316"}

尝试的错误代码 #

Declare @Input NVARCHAR(MAX) = N'{"Country":{"Layer4":[{"ItemName":"Cabinet MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Other MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"},{"ItemName":"Cabinet MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID":"b4283692-7c14-46da-9480-9a2976187316"}';
DECLARE @JSONOutput AS NVARCHAR(MAX);
DECLARE @JSONData AS NVARCHAR(MAX);

SET @JSONData = @Input; 

SELECT @JSONOutput = JSON_MODIFY(@JSONData, '$.Country.Layer4', JSON_QUERY('[]'))
SELECT @JSONOutput = JSON_MODIFY(@JSONOutput, 'append $.Country.Layer4', JSON_QUERY(@JSONData, '$.Country.Layer4[' + [key] + ']'))
FROM OPENJSON(@JSONData, '$.Country.Layer4')
WHERE JSON_VALUE([value], '$.ItemName') NOT IN('Cabinet MT', 'Other MT')
and JSON_VALUE([value], '$.ItemId') NOT IN ('cc3b0435-9ff5-4fd8-9f49-e049919a1414')

Print @JSONOutput

错误输出 #

{"Country": {"Layer4": [{"ItemName": "Cold MT","ItemId": "672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID": "b4283692-7c14-46da-9480-9a2976187316"}

问题原因 #

原代码的WHERE条件逻辑错误：用AND连接两个NOT IN，相当于同时排除所有Cabinet MT/Other MT的项，以及所有ItemId为cc3b0435-9ff5-4fd8-9f49-e049919a1414的项，这就把Cold MT且ItemId为该值的项也错误排除了，不符合需求。

正确逻辑应该是：保留那些不满足「(ItemName是Cabinet MT或Other MT) 并且 ItemId是指定值」的项，即反向筛选需要移除的条件。

修复后的代码 #

Declare @Input NVARCHAR(MAX) = N'{"Country":{"Layer4":[{"ItemName":"Cabinet MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Other MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"cc3b0435-9ff5-4fd8-9f49-e049919a1414"},{"ItemName":"Cold MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"},{"ItemName":"Cabinet MT","ItemId":"672f9a8c-71bb-4851-87de-e68154cabfad"}]},"CountryID":"b4283692-7c14-46da-9480-9a2976187316"}';
DECLARE @JSONOutput AS NVARCHAR(MAX);
DECLARE @JSONData AS NVARCHAR(MAX);

SET @JSONData = @Input; 

-- 先清空Layer4数组
SELECT @JSONOutput = JSON_MODIFY(@JSONData, '$.Country.Layer4', JSON_QUERY('[]'));

-- 重新添加符合条件的项
SELECT @JSONOutput = JSON_MODIFY(@JSONOutput, 'append $.Country.Layer4', JSON_QUERY([value]))
FROM OPENJSON(@JSONData, '$.Country.Layer4')
WHERE NOT (
    (JSON_VALUE([value], '$.ItemName') IN ('Cabinet MT', 'Other MT'))
    AND JSON_VALUE([value], '$.ItemId') = 'cc3b0435-9ff5-4fd8-9f49-e049919a1414'
);

Print @JSONOutput;

说明 #

• 修复后的WHERE条件使用NOT (...)包裹需要排除的逻辑：只有当ItemName是Cabinet MT或Other MT，同时ItemId等于指定值时，才排除该元素。
• 直接使用JSON_QUERY([value])比拼接数组索引更简洁且不易出错，因为OPENJSON返回的[value]本身就是每个数组元素的JSON字符串。

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