如何用MySQL提取嵌套JSON中指定层级的键值并格式化输出？

需求说明 #

从singular_reports_table表的XYZ列（存储嵌套JSON数据）中提取数据，输出为键名-7d: 数值的格式，同时过滤掉值为null的记录。

解决方案 #

方案1：MySQL 8.0+ 版本（推荐，自动适配所有键）

利用JSON_TABLE函数将JSON对象拆解为行数据，无需手动指定每个键：

SELECT 
  CONCAT(j.key_name, '-7d: ', j.value_7d) AS formatted_output
FROM 
  singular_reports_table,
  -- 提取JSON中所有顶级键，拆分为行
  JSON_TABLE(
    JSON_KEYS(XYZ),
    '$[*]' COLUMNS(
      key_name VARCHAR(255) PATH '$'
    )
  ) AS keys,
  -- 根据每个键提取对应的7d值，并转为整数格式
  JSON_TABLE(
    JSON_EXTRACT(XYZ, CONCAT('$.', keys.key_name)),
    '$' COLUMNS(
      value_7d DECIMAL(10,0) PATH '$.7d'
    )
  ) AS j
-- 过滤掉值为null的记录
WHERE 
  j.value_7d IS NOT NULL;

方案2：MySQL 5.7 版本（手动指定键）

由于5.7不支持JSON_TABLE，需手动枚举每个需要提取的键：

SELECT CONCAT('sng_ecommerce_purchase_revenue-7d: ', JSON_UNQUOTE(JSON_EXTRACT(XYZ, '$.sng_ecommerce_purchase_revenue.7d'))) AS formatted_output 
FROM singular_reports_table 
WHERE JSON_EXTRACT(XYZ, '$.sng_ecommerce_purchase_revenue.7d') IS NOT NULL

UNION ALL

SELECT CONCAT('unique_sng_content_view-7d: ', JSON_UNQUOTE(JSON_EXTRACT(XYZ, '$.unique_sng_content_view.7d'))) 
FROM singular_reports_table 
WHERE JSON_EXTRACT(XYZ, '$.unique_sng_content_view.7d') IS NOT NULL

UNION ALL

SELECT CONCAT('Unique_login_send_otp-7d: ', JSON_UNQUOTE(JSON_EXTRACT(XYZ, '$.Unique_login_send_otp.7d'))) 
FROM singular_reports_table 
WHERE JSON_EXTRACT(XYZ, '$.Unique_login_send_otp.7d') IS NOT NULL

UNION ALL

SELECT CONCAT('Unique_conversation_clicked-7d: ', JSON_UNQUOTE(JSON_EXTRACT(XYZ, '$.Unique_conversation_clicked.7d'))) 
FROM singular_reports_table 
WHERE JSON_EXTRACT(XYZ, '$.Unique_conversation_clicked.7d') IS NOT NULL;

说明 #

• 方案1会自动处理XYZ列中所有顶级键，后续新增键无需修改查询语句；
• 方案2需要手动维护要提取的键列表，适合键固定的场景；
• 两个方案都会过滤掉7d值为null的记录，最终输出格式与需求完全匹配。

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