如何用MySQL 8的JSON_TABLE动态提取JSON根键及嵌套值

针对示例JSON的直接查询 #

使用$.*作为JSON_TABLE的遍历路径，配合$."$key"获取根键名，$.A提取嵌套值：

SELECT
  json_key,
  a_value
FROM
  JSON_TABLE(
    '{"Foo": {"A": 3}, "Bar": {"A": 1, "B": 368}, "Biz": {"C": 2, "D": 10}}',
    '$.*' COLUMNS(
      json_key VARCHAR(255) PATH '$."$key"',
      a_value INTEGER PATH '$.A'
    )
  ) AS jt;

当JSON存储在数据库表中时 #

假设表名为json_data，JSON字段名为content，使用交叉连接关联JSON_TABLE：

SELECT
  jt.json_key,
  jt.a_value
FROM
  json_data,
  JSON_TABLE(
    json_data.content,
    '$.*' COLUMNS(
      json_key VARCHAR(255) PATH '$."$key"',
      a_value INTEGER PATH '$.A'
    )
  ) AS jt;

关键说明 #

• 原SQL错误原因：使用$作为JSON_TABLE的路径仅会生成一行数据，无法遍历所有根级元素；改用$.*可遍历每个根键对应的嵌套对象，每行对应一个根键。
• $."$key"是MySQL 8.0.14及以上版本支持的语法，用于获取当前遍历的JSON对象的键名。
• $.A直接从嵌套对象中提取"A"的值，若该键不存在则自动返回NULL，符合需求。

内容的提问来源于stack exchange，提问作者J-Deq87