Presto半结构化JSON处理：Unnest/Map/Cast问题求助

针对你的半结构化JSON展开需求，可以通过解析JSON为MAP结合多层UNNEST实现长格式输出，同时兼容不同行的键差异、空数组及Null值场景。

完整SQL代码 #

WITH test_data (id, messy_json) AS (
    VALUES 
        ('TEST_A', JSON '{"issue":[],"problem":[{"category":"math","id":2,"name":"subtraction"},{"category":"math","id":3,"name":"division"},{"category":"english","id":25,"name":"verbs"},{"category":"english","id":27,"name":"grammar"},{"category":"language","id":1,"name":"grammar"}],"version":4}'),
        ('TEST_B', JSON '{"problem":[],"version":4}'),
        ('TEST_C', JSON '{"version": 4, "problem": [], "issue": [null, null, null, null, null, null, null, null, null, null, null]}')
),
-- 步骤1：解析JSON为顶层键值MAP，遍历所有存在的顶层键
top_level AS (
    SELECT 
        id,
        top_key,
        top_value
    FROM test_data
    CROSS JOIN UNNEST(CAST(JSON_PARSE(messy_json) AS MAP(VARCHAR, JSON))) AS t(top_key, top_value)
),
-- 步骤2：区分顶层值是数组还是标量，展开数组元素并保留索引
array_expanded AS (
    SELECT 
        id,
        top_key,
        element_idx,
        element_value
    FROM top_level
    LEFT JOIN UNNEST(
        CASE WHEN JSON_TYPE(top_value) = 'ARRAY' THEN CAST(top_value AS ARRAY(JSON)) ELSE ARRAY[top_value] END
    ) WITH ORDINALITY AS e(element_value, element_idx) ON TRUE
    -- 过滤空数组，避免生成无意义空行
    WHERE NOT (JSON_TYPE(top_value) = 'ARRAY' AND JSON_ARRAY_LENGTH(top_value) = 0)
),
-- 步骤3：展开数组元素中的对象，统一处理标量与对象类型
final_flattened AS (
    SELECT 
        id,
        top_key,
        element_idx,
        COALESCE(inner_key, 'value') AS inner_key,
        COALESCE(
            JSON_EXTRACT_SCALAR(element_value, CONCAT('$.', inner_key)),
            JSON_EXTRACT_SCALAR(element_value, '$')
        ) AS inner_value
    FROM array_expanded
    LEFT JOIN UNNEST(
        CASE WHEN JSON_TYPE(element_value) = 'OBJECT' THEN CAST(element_value AS MAP(VARCHAR, JSON)) ELSE MAP(VARCHAR, JSON)() END
    ) AS i(inner_key, inner_value) ON TRUE
)
SELECT * FROM final_flattened
ORDER BY id, top_key, element_idx, inner_key;

核心逻辑说明 #

1. 顶层键遍历：通过CAST(JSON_PARSE(messy_json) AS MAP(VARCHAR, JSON))将JSON转为键值对MAP，自动适配不同行的键差异，不会遗漏任何顶层键。
2. 数组与标量统一处理：用JSON_TYPE判断值类型，数组则展开元素并通过WITH ORDINALITY保留索引；标量则包装为单元素数组，保证逻辑一致性。
3. 内层对象展开：数组中的JSON对象转为MAP再次展开键值；非对象类型（如Null、数字）直接提取标量值，用value作为默认键名统一格式。
4. 空值过滤：主动过滤空数组，避免生成无效行；包含Null元素的数组（如TEST_C的issue）会保留每个Null元素的条目，确保数据完整性。

输出示例（部分） #

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