如何从MySQL JSON字段的数组中提取label与field_value键值对

解决方案 #

方案1：MySQL 8.0.4+ 最优实现（展开为独立行） #

直接使用原生JSON_TABLE解析JSON数组提取目标字段，单条查询即可得到预期的多行结果：

SELECT 
  CONCAT(jt.label, ': ', jt.field_value) AS result
FROM some_table st,
     JSON_TABLE(
       st.data,
       '$.fields[*]' COLUMNS (
         label VARCHAR(255) PATH '$.label',
         field_value VARCHAR(255) PATH '$.field_value'
       )
     ) AS jt;

运行后输出：

E-mail: test@example.com
Imię: Aneta

方案2：单字段返回带换行的拼接字符串 #

如果需要将同一条源数据对应的所有键值对合并为一个字段返回，可搭配GROUP_CONCAT实现：

SELECT 
  GROUP_CONCAT(CONCAT(jt.label, ': ', jt.field_value) SEPARATOR '\n') AS result
FROM some_table st,
     JSON_TABLE(
       st.data,
       '$.fields[*]' COLUMNS (
         label VARCHAR(255) PATH '$.label',
         field_value VARCHAR(255) PATH '$.field_value'
       )
     ) AS jt
GROUP BY st.id; -- 按源表主键分组即可

方案3：MySQL 5.7兼容实现 #

针对不支持JSON_TABLE的旧版本MySQL，可通过构造序列表辅助解析，单条查询同样可以实现：

SELECT 
  CONCAT(
    JSON_UNQUOTE(JSON_EXTRACT(st.data, CONCAT('$.fields[', n.idx, '].label'))),
    ': ',
    JSON_UNQUOTE(JSON_EXTRACT(st.data, CONCAT('$.fields[', n.idx, '].field_value')))
  ) AS result
FROM some_table st
JOIN (
  SELECT 0 idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 -- 按实际数组最大长度扩展即可
) n ON n.idx < JSON_LENGTH(st.data->'$.fields');

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