MySQL8 JSON列如何提取拼接JSON对象中的所有字符串类型值

解决方案 #

MySQL 8.0.19及以上版本可以通过JSON_TABLE+GROUP_CONCAT的组合实现需求，这是目前最简方案，示例SQL如下：
假设你的表名为your_table，存储JSON的列名为json_col：

SELECT GROUP_CONCAT(JSON_UNQUOTE(j.val) SEPARATOR ' ') AS concat_result
FROM your_table,
JSON_TABLE(
  your_table.json_col,
  -- 遍历JSON根节点下的所有值
  '$.*' COLUMNS (
    val JSON PATH '$'
  )
) AS j
-- 筛选值类型为字符串的条目
WHERE JSON_TYPE(j.val) = 'STRING';

实现逻辑说明 #

• 用JSON_TABLE将JSON对象中所有值拆分为单独的行数据
• 用JSON_TYPE判断每个值的类型，仅保留字符串类型的值
• 用JSON_UNQUOTE去掉JSON字符串自带的引号包裹
• 用GROUP_CONCAT以空格为分隔符拼接所有符合要求的字符串

拿你给出的示例JSON测试，上述语句返回结果就是Something123 Hi world!，完全符合预期。
如果你的MySQL版本低于8.0.19不支持JSON_TABLE，需要写存储过程遍历JSON的所有键逐个判断类型拼接，复杂度会高很多，建议优先升级到支持JSON_TABLE的版本使用上述方案。

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