使用Presto SQL展开未知键名的键值对列（值为数组）

解决方案思路 #

由于外层键名动态无法提前预知，且仅需保留值为非空数组的键，核心处理逻辑为：

• 将JSON对象转换为键值对条目集合，避开硬编码键名的限制
• 过滤掉值为空数组的条目
• 逐层展开数组及嵌套的键值对结构

场景1：嵌套键名固定 #

如果嵌套对象的键（如示例中的nestedKey1、nestedKey2）是固定的，可直接提取对应值：

SELECT
    t.id, -- 替换为你的表中唯一标识列
    outer_entry.key AS outer_dynamic_key,
    -- 提取并转换嵌套键对应的值
    CAST(json_extract_scalar(nested_obj, '$.nestedKey1') AS INTEGER) AS nested_key1,
    CAST(json_extract_scalar(nested_obj, '$.nestedKey2') AS INTEGER) AS nested_key2
FROM your_table t
-- 过滤NULL的JSON列
WHERE t.json_column IS NOT NULL
-- 将JSON转为键值对条目数组，展开为行
CROSS JOIN UNNEST(map_entries(cast(t.json_column AS MAP(VARCHAR, ARRAY<JSON>)))) AS outer_entries(outer_entry)
-- 仅保留值为非空数组的外层键
WHERE cardinality(outer_entry.value) > 0
-- 展开外层键对应的非空数组，得到每个嵌套对象
CROSS JOIN UNNEST(outer_entry.value) AS nested_objs(nested_obj);

场景2：嵌套键名也动态 #

如果嵌套对象的键同样是动态的，需要再次将嵌套对象转为键值对条目展开：

SELECT
    t.id, -- 替换为你的表中唯一标识列
    outer_entry.key AS outer_dynamic_key,
    nested_entry.key AS nested_dynamic_key,
    CAST(nested_entry.value AS INTEGER) AS nested_value
FROM your_table t
WHERE t.json_column IS NOT NULL
CROSS JOIN UNNEST(map_entries(cast(t.json_column AS MAP(VARCHAR, ARRAY<JSON>)))) AS outer_entries(outer_entry)
WHERE cardinality(outer_entry.value) > 0
CROSS JOIN UNNEST(outer_entry.value) AS nested_objs(nested_obj)
-- 将嵌套对象转为键值对条目数组并展开
CROSS JOIN UNNEST(map_entries(cast(nested_obj AS MAP(VARCHAR, INTEGER)))) AS nested_entries(nested_entry);

关键函数说明 #

• cast(json_column AS MAP(VARCHAR, ARRAY<JSON>))：将JSON对象转换为Presto原生MAP类型，适配动态键名的处理
• map_entries(...)：将MAP转为包含key和value的ROW数组，实现键值对的结构化拆分
• UNNEST(...)：将数组展开为多行，实现层级数据的扁平化
• cardinality(...)：计算数组长度，用于过滤空数组条目
• json_extract_scalar(...)：从JSON对象中提取指定键的字符串值，再转换为目标类型（如INTEGER）

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