如何在MySQL中提取JSON字符串的动态键与对应thirdkey值

问题场景 #

MySQL表某单元格存储如下格式的JSON数据，其中包含数百个动态更新的keyN类顶层键，需要提取所有keyN及其对应的thirdkey值，生成Grafana表格且无需手动维护这些键：

{
  "key1": {
    "otherkey":1,
    "someotherkey": 2,
    "thirdkey": 3
  },
  "key2": {
    "otherkey":2,
    "someotherkey": 1,
    "thirdkey": 0
  },
  "key3": {
    "otherkey":2,
    "someotherkey": 0,
    "thirdkey": 2
  }
}

解决方案（MySQL 8.0+） #

使用MySQL内置的JSON_KEYS和JSON_TABLE函数，实现动态提取所有顶层键及对应值，无需硬编码键名：

核心SQL语句 #

假设表名为your_table，存储JSON的列名为json_column，若需指定某一行可添加WHERE条件：

SELECT
  j.key_name,
  JSON_UNQUOTE(JSON_EXTRACT(t.json_column, CONCAT('$.', j.key_name, '.thirdkey'))) AS thirdkey_value
FROM
  your_table t,
  JSON_TABLE(
    JSON_KEYS(t.json_column),
    '$[*]' COLUMNS (key_name VARCHAR(255) PATH '$')
  ) j
-- 如需定位单行数据，添加WHERE条件，例如：WHERE t.id = 1

语句解析 #

• JSON_KEYS(t.json_column)：提取JSON列中所有顶层键（key1、key2...keyN），返回JSON数组格式。
• JSON_TABLE(...)：将JSON数组拆分为行结构，每个行的key_name字段对应一个顶层键名。
• CONCAT('$.', j.key_name, '.thirdkey')：动态拼接JSON路径，例如对key1生成路径$.key1.thirdkey。
• JSON_EXTRACT：根据拼接的路径提取thirdkey的值，JSON_UNQUOTE去除结果的引号（统一处理字符串/数字类型值）。

Grafana配置 #

将上述SQL作为Grafana的MySQL数据源查询语句，在表格面板中选择key_name和thirdkey_value作为展示列即可，后续JSON中新增/删除keyN时，无需修改查询语句，表格会自动同步更新。

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