MySQL8.0下基于外键ID数组提取关联路径JSON数组的实现问题

核心实现依赖MySQL 8.0支持的JSON_ARRAYAGG()聚合函数，将关联匹配到的多行path值合并为单个JSON数组。

方法1：基于LATERAL JOIN实现 #

原有LATERAL查询的逻辑无需大幅调整，只需在外层按t1.id分组后做聚合即可：

SELECT 
  t1.id,
  JSON_ARRAYAGG(t2.path) AS paths
FROM t1,
LATERAL (
  SELECT path 
  FROM t2 
  WHERE JSON_CONTAINS(t1.array, CAST(t2.id AS JSON))
) AS t2
GROUP BY t1.id;

方法2：简化的JOIN实现 #

也可以直接把JSON匹配作为关联条件，写法更简洁：

SELECT 
  t1.id,
  JSON_ARRAYAGG(t2.path) AS paths
FROM t1
INNER JOIN t2 
  ON JSON_CONTAINS(t1.array, CAST(t2.id AS JSON))
GROUP BY t1.id;

边界场景适配 #

如果需要保留t1中无匹配t2记录的行，返回空JSON数组，可以调整为左连接+空值处理：

SELECT 
  t1.id,
  IFNULL(JSON_ARRAYAGG(t2.path), JSON_ARRAY()) AS paths
FROM t1
LEFT JOIN t2 
  ON JSON_CONTAINS(t1.array, CAST(t2.id AS JSON))
GROUP BY t1.id;

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