已知table_b的ID，如何查询对应table_a的数据？MySQL JSON数组查询问题

原SQL无效的原因是json_extract返回的是完整的JSON数组对象（如[1,2]），而IN子句需要离散的数值列表，无法直接匹配。以下是不同数据库环境下的可行方案：

MySQL 环境 #

方法1：使用JSON_TABLE拆分JSON数组（推荐，性能更优） #

将JSON数组转换为行数据后关联查询：

SELECT a.*
FROM table_a a
INNER JOIN (
    SELECT j.id
    FROM table_b b
    INNER JOIN JSON_TABLE(
        b.table_a_ids,
        '$[*]' COLUMNS(id INT PATH '$')
    ) j
    WHERE b.id = 1
) t ON a.id = t.id;

方法2：使用JSON_CONTAINS判断包含关系 #

通过JSON函数直接判断table_a.id是否存在于目标数组中：

SELECT *
FROM table_a a
WHERE EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.id = 1
    AND JSON_CONTAINS(b.table_a_ids, CAST(a.id AS JSON))
);

PostgreSQL 环境 #

方法1：使用json_array_elements拆分JSON数组 #

将JSON数组拆分为单行数值后关联：

SELECT a.*
FROM table_b b
JOIN json_array_elements(b.table_a_ids) AS j(id)
ON b.id = 1
JOIN table_a a ON a.id = j.id::integer;

方法2：字符串处理拆分数组（兼容旧版本） #

通过字符串转换拆分数组：

SELECT a.*
FROM table_a a
INNER JOIN (
    SELECT trim(unnest(string_to_array(b.table_a_ids::text, ',')), '[]')::integer AS id
    FROM table_b b
    WHERE b.id = 1
) t ON a.id = t.id;

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