SQL中如何筛选包含所有指定名称的JSON数组列？

核心思路 #

不用分开处理单个/多个名称，统一把传入的@Names拆分成表格式的名称列表，再通过对比JSON数组与该列表的匹配情况实现需求：

• 单个名称：判断列表中的唯一名称存在于JSON数组
• 多个名称：确保JSON数组包含列表中的所有名称

具体实现 #

假设你的表名为YourTable，JSON列名为Names，存储过程的参数为@Names NVARCHAR(MAX)（允许NULL，代表无过滤）。

步骤1：拆分传入的名称列表

用STRING_SPLIT将逗号分隔的字符串拆成表（SQL Server 2016及以上支持），同时处理名称前后可能的空格：

DECLARE @NameList TABLE (Name NVARCHAR(100))
INSERT INTO @NameList
SELECT TRIM(value) FROM STRING_SPLIT(@Names, ',') WHERE TRIM(value) <> ''

步骤2：编写通用查询逻辑

合并单个/多个名称的判断逻辑，一次满足两种场景：

SELECT *
FROM YourTable a
WHERE 
    -- 无传入参数时返回所有行
    (@Names IS NULL OR @Names = '')
    OR
    -- 确保JSON数组包含所有指定名称
    NOT EXISTS (
        SELECT Name FROM @NameList
        EXCEPT
        SELECT value FROM OPENJSON(a.Names)
    )

这个逻辑的核心是：如果拆分后的名称列表减去JSON数组中的名称后没有剩余，说明JSON数组包含所有指定名称；如果是单个名称，就等价于判断该名称存在于数组中。

现有代码的优化 #

你原来处理单个名称的逻辑可以简化为更高效的JSON_CONTAINS写法：

JSON_CONTAINS(a.Names, QUOTENAME(@Names, '"')) OR @Names IS NULL

QUOTENAME的作用是给名称加上双引号，符合JSON字符串的格式要求，避免匹配错误。

为什么不推荐用LIKE？ #

用LIKE '%Jimmy%'这类方式存在两个关键问题：

• 误匹配风险：比如JSON数组中有"Jimmyne"，会被LIKE '%Jimmy%'命中，但实际不是目标名称
• 多名称匹配困难：需要写一堆AND LIKE条件，且依然存在子串匹配问题，准确性和维护性都很差

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