MySQL查询提取未知键名嵌套JSON的单键对象数据

问题核心 #

现有SQL仅校验JSON第一层的键数量，无法递归穿透嵌套JSON结构，同时存在函数拼写错误、路径拼接语法错误，无法满足「未知键名、嵌套结构下筛选仅含单个有效叶子键值对条目、输出完整键路径+叶子值」的需求。
注：有效键值对指最终值为非对象类型的标量值，从根节点到叶子节点的每一层嵌套都仅包含1个键。

适用MySQL 8.0+的实现代码 #

用递归CTE实现多层嵌套JSON的遍历，逐层校验键数量，直到定位到叶子值：

WITH RECURSIVE json_parser AS (
    -- 初始化：筛掉第一层键数≠1的条目，提取第一层键和值
    SELECT 
        id,
        jt.`field` AS field_path,
        JSON_EXTRACT(`column`, CONCAT('$.', jt.`field`)) AS field_value
    FROM `table`
    JOIN JSON_TABLE(
        JSON_KEYS(`column`),
        '$[*]' COLUMNS(`field` VARCHAR(191) PATH '$')
    ) AS jt
    WHERE JSON_LENGTH(`column`) = 1

    UNION ALL

    -- 递归向下遍历：当前值为单键对象时，继续提取下一层键和值
    SELECT 
        p.id,
        CONCAT(p.field_path, '.', jt.`field`) AS field_path,
        JSON_EXTRACT(p.field_value, CONCAT('$.', jt.`field`)) AS field_value
    FROM json_parser p
    JOIN JSON_TABLE(
        JSON_KEYS(p.field_value),
        '$[*]' COLUMNS(`field` VARCHAR(191) PATH '$')
    ) AS jt
    WHERE JSON_TYPE(p.field_value) = 'OBJECT' 
      AND JSON_LENGTH(p.field_value) = 1
)
-- 格式化输出最终结果，仅保留非对象类型的叶子节点
SELECT 
    CONCAT(
        ROW_NUMBER() OVER(ORDER BY id),
        ':', field_path,
        ',1,',
        JSON_QUOTE(field_value)
    ) AS output
FROM json_parser
WHERE JSON_TYPE(field_value) != 'OBJECT';

逻辑说明 #

• 递归过程中每进入一层，都会先校验当前JSON对象的键数量是否为1，任意一层出现多键的条目会被自动排除
• 遍历过程中自动拼接完整的嵌套键路径，直到遇到非对象类型的标量值（字符串、日期、数字等）停止递归
• 结果自动按要求格式化，序号、路径、长度标记、带引号的值和预期输出完全对齐

测试效果 #

针对给出的3条样例数据：

• {"key1": "2022-06-22", "key2": "2022-06-25"}：第一层键数为2，初始化阶段直接被过滤
• {"key12": "2022-06-21"}：第一层为单键+字符串标量，输出1:key12,1,"2022-06-21"
• {"key13": {"key131": "2022-06-01"}}：两层单键嵌套，最终定位到叶子值，输出2:key13.key131,1,"2022-06-01"

原SQL错误点 #

• 函数拼写错误：MySQL原生无JSON_VALUES函数，提取JSON值需用JSON_EXTRACT/JSON_VALUE
• 路径语法错误：JSON路径参数无需额外包裹单引号，原拼接逻辑会导致路径解析失败
• 缺少递归遍历逻辑，无法处理嵌套JSON结构
• 未校验嵌套层的键数量，无法识别嵌套多键的无效条目

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