低于8.0版本的MySQL如何将JSON数组指定字段值逗号拼接返回

适用版本：MySQL 5.7 ~ 8.0之前的版本 #

MySQL 5.7已支持基础JSON操作函数，仅缺少8.0新增的JSON_TABLE等高级特性，可通过以下方案实现需求：

• 核心思路：生成连续数字序列匹配JSON数组的下标（JSON数组下标从0开始），逐个取出first字段值后用GROUP_CONCAT拼接

示例查询（临时生成数字序列，适用于数组最大长度不超过10的场景） #

SELECT 
  t.id,
  GROUP_CONCAT(
    JSON_UNQUOTE(JSON_EXTRACT(t.owners, CONCAT('$[', n.n, '].first')))
    SEPARATOR ','
  ) AS owners
FROM your_table t
LEFT JOIN (
  -- 可根据实际数组最大长度追加更多数字
  SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
  SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
  SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) n ON n.n < JSON_LENGTH(t.owners)
GROUP BY t.id

使用时将your_table替换为实际表名即可，如果数组长度超过10，只需在子查询n中追加更多UNION ALL SELECT 数字即可。

优化方案（数组长度不确定/过长的场景） #

提前创建一张永久数字辅助表numbers，存储0到足够大的连续整数（比如到1000，覆盖所有可能的数组长度），后续查询直接调用即可：

-- 仅需执行一次创建辅助表
CREATE TABLE numbers (n INT PRIMARY KEY);
-- 插入足够多的连续数字，示例插入0~999
INSERT INTO numbers (n)
SELECT (h*100 + t*10 + u) AS n
FROM 
  (SELECT 0 h UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) h,
  (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t,
  (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) u;

-- 后续查询直接使用辅助表
SELECT 
  t.id,
  GROUP_CONCAT(
    JSON_UNQUOTE(JSON_EXTRACT(t.owners, CONCAT('$[', n.n, '].first')))
    SEPARATOR ','
  ) AS owners
FROM your_table t
LEFT JOIN numbers n ON n.n < JSON_LENGTH(t.owners)
GROUP BY t.id

特殊场景：MySQL版本低于5.7（无原生JSON支持） #

该场景仅能通过字符串截取实现，对JSON格式要求严格，格式变动会导致结果错误，示例如下：

SELECT 
  id,
  TRIM(BOTH ',' FROM REPLACE(
    REPLACE(
      SUBSTRING_INDEX(
        SUBSTRING_INDEX(owners, '"first":"', -1*(LENGTH(owners) - LENGTH(REPLACE(owners, '"first":"', '')))/7 + 1),
        '","last"', 10000
      ),
      '","last":"', ','
    ),
    '"}]', ''
  )) AS owners
FROM your_table;

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