如何在PrestoSQL中提取JSON的父子级所有键？

AI 小北

2026-7-28

解决PrestoSQL提取嵌套JSON所有层级键的问题

要提取JSON所有层级的键（包括嵌套对象的子键），map_keys只能处理顶层键，需要用递归CTE遍历所有嵌套层级。以下是针对你的数据的完整解决方案：

步骤1：处理JSON格式

你的原始JSON使用单引号，而标准JSON要求双引号，所以先通过replace转换格式，再用json_parse解析。

步骤2：递归遍历所有键

用递归CTE逐层提取JSON对象的键，直到没有嵌套对象为止：

WITH RECURSIVE json_keys AS (
    -- 初始层：解析顶层JSON，提取顶层键
    SELECT
        json_parse(replace(raw_json, '''', '"')) AS json_obj,
        map_keys(json_parse(replace(raw_json, '''', '"'))) AS keys
    FROM (
        -- 替换为你的实际数据源，若为表则直接引用表名
        VALUES 
            ('{\'a\': 2, \'b\': {\'b1\': 3, \'b2\': 5}}'),
            ('{\'c\': 3, \'a\': 5}'),
            ('{\'d\': 1, \'c\': 7}')
    ) AS json_data(raw_json)
    UNION ALL
    -- 递归层：处理嵌套的JSON对象，提取子键
    SELECT
        json_extract(json_obj, concat('$.', key)) AS json_obj,
        map_keys(json_extract(json_obj, concat('$.', key))) AS keys
    FROM json_keys
    CROSS JOIN UNNEST(keys) AS t(key)
    -- 仅处理值为JSON对象的键，避免非对象类型报错
    WHERE json_type(json_extract(json_obj, concat('$.', key))) = 'OBJECT'
)
-- 收集所有键，去重后拼接成单行
SELECT string_agg(DISTINCT key, ', ') AS all_keys
FROM json_keys
CROSS JOIN UNNEST(keys) AS t(key);