使用JSON_EXTRACT从JSON列表提取foo值匹配表列A的SQL查询方法

核心思路是先将JSON数组拆分为单行数据，提取每个元素的foo字段后做匹配，优先使用EXISTS判断，性能比WHERE IN写法更优。不同数据库的具体实现如下：

MySQL 实现（5.7及以上版本） #

• 支持JSON_TABLE的高版本写法：

SELECT t.*
FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    FROM JSON_TABLE(
        t.B,
        '$[*]' COLUMNS (foo_val VARCHAR(255) PATH '$.foo')
    ) AS jt
    WHERE jt.foo_val = t.A
);

• 不支持JSON_TABLE的低版本可以用JSON_SEARCH简化判断：

SELECT *
FROM 你的表名
WHERE JSON_SEARCH(B, 'one', A, NULL, '$[*].foo') IS NOT NULL;

JSON_SEARCH参数说明：第二个参数one表示找到第一个匹配结果就返回，最后一个路径参数指定匹配数组下所有元素的foo字段，返回非空即代表存在匹配项。

PostgreSQL 实现 #

如果B列存储的是字符串类型，需要先转成jsonb/json类型再处理：

SELECT t.*
FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.B::jsonb) AS elem
    WHERE elem->>'foo' = t.A
);

如果需要用WHERE IN写法也可以实现，但性能比EXISTS差，仅小数据量场景适用：

SELECT *
FROM 你的表名
WHERE A IN (
    SELECT elem->>'foo'
    FROM 你的表名, jsonb_array_elements(B::jsonb) AS elem
)

SQL Server 实现 #

用OPENJSON拆分数组后匹配：

SELECT t.*
FROM 你的表名 t
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON(t.B)
    WITH (
        foo_val VARCHAR(255) '$.foo'
    ) AS jt
    WHERE jt.foo_val = t.A
);

优化建议 #

• 如果B列存储的是字符串格式JSON，查询前需要确保JSON格式合法，避免类型转换报错
• 数据量较大的场景可以给JSON字段的foo值建立函数索引，比如PostgreSQL建GIN索引、MySQL给生成的foo列建索引，能大幅提升查询性能

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