如何用INNER JOIN关联含数组ID的两张表且不修改表结构

由于promos.contracts_id是JSON数组格式，直接用等值判断关联会失败，需要借助数据库自带的JSON数组处理函数实现关联，以下是主流数据库的具体方案：

MySQL/MariaDB 实现 #

使用JSON_CONTAINS函数判断contract的ID是否存在于目标数组中：

SELECT p.*, c.name
FROM promos p
INNER JOIN contract c 
ON JSON_CONTAINS(p.contracts_id, CAST(c.id AS JSON), '$')

• 补充：如果contract.id是字符串类型，可替换第二个参数为'"' || c.id || '"'，省去类型转换步骤。

PostgreSQL 实现 #

PostgreSQL支持两种关联方式，按需选择：

方式1：数组包含判断 #

SELECT p.*, c.name
FROM promos p
INNER JOIN contract c 
ON p.contracts_id::jsonb @> to_jsonb(c.id::text)::jsonb

方式2：展开数组后关联（适合拆分显示每个关联记录） #

SELECT p.*, c.name
FROM promos p
CROSS JOIN json_array_elements_text(p.contracts_id) AS arr(contract_id)
INNER JOIN contract c ON arr.contract_id = c.id::text

• 说明：json_array_elements_text会将JSON数组拆分为多行，每个数组元素对应一行，最终每个promo会与关联的每个contract生成一条独立记录。

SQLite 实现 #

需先启用JSON扩展，再通过json_each展开数组关联：

SELECT p.*, c.name
FROM promos p
JOIN json_each(p.contracts_id) AS j
INNER JOIN contract c ON j.value = c.id

注意事项 #

• 如果contracts_id存储的是JSON格式的字符串（非数据库原生JSON类型），大部分数据库会自动识别格式，若出现异常可先通过JSON_VALID（MySQL）或类型转换函数验证格式合法性。
• 这种JSON关联的性能远低于标准外键关联，若数据量较大，仍建议修改表结构，建立promos_contracts中间关联表（存储promo_id与contract_id的一对一映射）。

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