SQL Server 2016如何查询JSON数组中所有符合cName条件的记录

你原有写法仅查询数组下标为0的元素是因为路径中写死了[0]，要遍历全数组可以根据你使用的数据库类型选择对应写法：

MySQL #

使用JSON_SEARCH函数匹配数组所有元素：

SELECT *
FROM CD_Name_List
WHERE JSON_SEARCH(MemberJSON, 'one', 'Kay', NULL, '$.NameDetailInfo[*].cName') IS NOT NULL;

参数说明：

• 第二个参数'one'代表找到第一个匹配项就返回，如需返回所有匹配项可替换为'all'
• 路径$.NameDetailInfo[*].cName中的[*]是通配符，代表匹配数组下的所有元素

SQL Server #

使用OPENJSON将数组拆解为行集后筛选：

SELECT *
FROM CD_Name_List
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(MemberJSON, '$.NameDetailInfo')
    WITH (
        cName VARCHAR(50) '$.cName'
    ) AS json_arr
    WHERE json_arr.cName = 'Kay'
);

PostgreSQL #

使用jsonb_array_elements（JSON类型用json_array_elements）拆解数组后筛选：

SELECT *
FROM CD_Name_List
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(MemberJSON->'NameDetailInfo') AS elem
    WHERE elem->>'cName' = 'Kay'
);

如果表数据量较大，建议针对JSON对应检索字段建立专用索引，可大幅提升查询效率：

• MySQL可建立JSON函数索引
• SQL Server可建立JSON属性索引
• PostgreSQL可建立GIN索引

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