MySQL中如何判断两个JSON数组存在任意交集而非全部匹配？

要判断两个JSON数组是否有至少一个共同元素，可以通过JSON_TABLE将数组转换为行数据，再通过关联查询判断是否存在重合值，具体实现如下：

方法一：使用JSON_TABLE + EXISTS子查询 #

针对你给出的示例，可执行以下SQL：

SET @j1 = '["a","b","c"]';
SET @j2 = '["d","e","f","a"]';

SELECT EXISTS(
    SELECT 1
    FROM JSON_TABLE(@j1, '$[*]' COLUMNS(val VARCHAR(255) PATH '$')) AS t1
    JOIN JSON_TABLE(@j2, '$[*]' COLUMNS(val VARCHAR(255) PATH '$')) AS t2
    ON t1.val = t2.val
) AS has_intersection;

执行后会返回1（存在交集）或0（无交集），完全符合你需要的判断逻辑。

表字段场景下的用法 #

如果是查询表中两个JSON数组字段存在交集的行，比如表test有json_col1和json_col2两个JSON数组字段，可这样写：

SELECT *
FROM test
WHERE EXISTS(
    SELECT 1
    FROM JSON_TABLE(json_col1, '$[*]' COLUMNS(val VARCHAR(255) PATH '$')) AS t1
    JOIN JSON_TABLE(json_col2, '$[*]' COLUMNS(val VARCHAR(255) PATH '$')) AS t2
    ON t1.val = t2.val
);

原理说明 #

JSON_TABLE函数会把JSON数组拆分成每行对应一个元素的临时表，通过两个临时表的等值关联，只要存在匹配的元素，EXISTS就会返回1，以此判断数组是否存在交集。

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