MySQL如何将JSON对象内的键值对提取为多列对应值输出

方案1：固定键名场景（已知groups下的所有键） #

如果提前确认groups下只会出现CS、Physics、Chemistry三个键，直接用JSON访问运算符即可，代码最简单：

SELECT
  id,
  jdoc->>'$.groups.CS' AS CS,
  jdoc->>'$.groups.Physics' AS Physics,
  jdoc->>'$.groups.Chemistry' AS Chemistry
FROM t3;

说明：->>是JSON_UNQUOTE(JSON_EXTRACT())的简写，返回的结果会自动去掉JSON值的引号，返回数值类型结果。

方案2：动态键名场景（groups下的键不固定） #

如果groups下的键会动态增减，需要用预处理语句动态拼接查询SQL，支持自动适配所有已存在的键：

-- 步骤1：提取所有groups下的键，拼接为查询字段
SET @query_sql = NULL;
SELECT GROUP_CONCAT(
  DISTINCT CONCAT('jdoc->>\'$.groups.', `key`, '\' AS ', `key`)
) INTO @query_sql
FROM t3,
JSON_TABLE(JSON_KEYS(jdoc, '$.groups'), '$[*]' COLUMNS (`key` VARCHAR(255) PATH '$')) AS json_keys;

-- 步骤2：拼接完整查询语句
SET @query_sql = CONCAT('SELECT id, ', @query_sql, ' FROM t3');

-- 步骤3：执行动态SQL
PREPARE exec_stmt FROM @query_sql;
EXECUTE exec_stmt;
DEALLOCATE PREPARE exec_stmt;

说明：该方案依赖MySQL 8.0及以上版本提供的JSON_TABLE函数，更低版本MySQL建议升级或者使用固定键名方案。

调整JSON格式的优化方案 #

如果允许修改jdoc的存储结构，建议将groups存储为键值对数组格式，后续扩展更灵活，示例结构如下：

{"groups": [{"name":"CS","value":15}, {"name":"Physics","value":20}, {"name":"Chemistry","value":10}]}

该格式下可以直接用JSON_TABLE展开为行列结构，适合做过滤、聚合等复杂操作，展开查询示例：

SELECT 
  t.id,
  j.name,
  j.value
FROM t3 t,
JSON_TABLE(t.jdoc, '$.groups[*]' COLUMNS(
  name VARCHAR(255) PATH '$.name',
  value INT PATH '$.value'
)) j;

如需转成宽表输出，结合上面的动态SQL拼接逻辑即可实现。

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