SQL对JSON数组内每个ID执行JOIN关联的实现方法

约束适配说明 #

以下实现完全符合业务要求：

• 不需要修改writers表projects字段的JSON数据类型，兼容现有服务端读取逻辑
• 单条SQL完成所有逻辑，不需要应用层遍历JSON数组多次发起数据库请求
• 支持索引优化，满足分页场景的性能要求

标准高效实现（基于SQL/JSON标准函数JSON_TABLE） #

MariaDB 10.6已完整支持SQL:2016标准的JSON_TABLE函数，可以直接在数据库内核层面将JSON数组拆解为关系型行数据，再做常规表关联，比JSON_CONTAINS+EXISTS的写法性能更好、语义更清晰。

SELECT c.*
FROM writers w
-- 拆解JSON数组为项目ID关系表
JOIN JSON_TABLE(
  w.projects,
  '$[*]' COLUMNS (
    project_id VARCHAR(32) PATH '$' -- 字段类型和长度与cities表的project字段保持一致即可
  )
) AS parsed_projects
-- 关联匹配对应城市
JOIN cities c ON c.project = parsed_projects.project_id
WHERE w.id = 8
-- 如果id是writers表主键，name='Mike'的条件可按需保留做冗余校验
ORDER BY c.name ASC;

性能优化建议 #

• 给cities表的project字段创建普通索引，关联时可以直接走索引匹配，避免全表扫描
• 分页时直接在语句末尾追加LIMIT 页大小 OFFSET 偏移量即可，数据库会在关联阶段就提前终止扫描，不会拉取全量数据

旧写法的性能问题 #

之前使用的JSON_CONTAINS+EXISTS写法逻辑上可以得到正确结果，但存在明显性能缺陷：

-- 旧写法示例（不推荐）
SELECT c.*
FROM cities c
WHERE EXISTS (
  SELECT 1 
  FROM writers w 
  WHERE w.id = 8 
    AND JSON_CONTAINS(w.projects, JSON_QUOTE(c.project))
)
ORDER BY c.name ASC;

该写法需要遍历cities表的每一行数据，逐一执行JSON包含判断，完全无法利用cities.project字段的索引，表数据量变大后分页查询性能会急剧下降。

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