MySQL如何将JSON_ARRAYAGG返回的JSON数组用于WHERE IN子句？

方案1：使用JSON_CONTAINS函数（兼容MySQL 5.7及以上版本，最简单实现） #

这是最直接的替代方案，直接判断目标值是否存在于JSON数组中，语法如下：

SELECT si.item_name
FROM sourcing_item si
-- 注意参数顺序：第一个参数是JSON数组，第二个参数是转成JSON类型的目标值
WHERE JSON_CONTAINS(
    (SELECT JSON_ARRAYAGG(ID) FROM sourcing_item), 
    CAST(si.ID AS JSON)
);

如果你的item_array是同一条查询里的聚合结果，需要结合子查询或者CTE使用：

WITH agg_result AS (
    SELECT COUNT(si.ID) AS item_count, JSON_ARRAYAGG(si.ID) AS item_array
    FROM sourcing_item si
)
SELECT si.item_name
FROM sourcing_item si, agg_result ar
WHERE JSON_CONTAINS(ar.item_array, CAST(si.ID AS JSON));

方案2：使用JSON_TABLE拆解JSON数组为行（兼容MySQL 8.0及以上版本，性能更优，适合复杂场景） #

如果需要处理大量数据，或者后续还要对数组值做其他运算，可以先把JSON数组转成临时的关系表，再用IN或者JOIN匹配：

WITH agg_result AS (
    SELECT JSON_ARRAYAGG(ID) AS item_array
    FROM sourcing_item
),
-- 把JSON数组拆解成每行一个ID的临时表
id_list AS (
    SELECT id FROM JSON_TABLE(
        (SELECT item_array FROM agg_result),
        '$[*]' COLUMNS(id INT PATH '$')
    ) AS t
)
SELECT si.item_name
FROM sourcing_item si
WHERE si.ID IN (SELECT id FROM id_list);

方案3：转换为逗号分隔字符串搭配FIND_IN_SET（兼容低版本，性能一般） #

如果是临时查询且数据量很小，也可以把JSON数组的括号去掉转成普通逗号分隔字符串，用FIND_IN_SET匹配：

WITH agg_result AS (
    SELECT JSON_UNQUOTE(JSON_ARRAYAGG(ID)) AS item_str
    FROM sourcing_item
)
SELECT si.item_name
FROM sourcing_item si, agg_result ar
WHERE FIND_IN_SET(
    si.ID, 
    REPLACE(REPLACE(ar.item_str, '[', ''), ']', '')
);

注意：该方案性能较差，不推荐用于大表查询，仅做兼容场景备选。

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