未知JSON键场景下如何用Presto SQL生成新旧值对比表

实现思路 #

你的JSON结构规律为每个顶级键对应的值均为长度为2的数组：

• 若数组元素为基础类型（布尔、字符串、数字）：索引0为旧值，索引1为新值
• 若数组元素为JSON对象：索引0为旧属性集合、索引1为新属性集合，需拆分对象内部的KV，路径用#拼接父子键
  因为键不固定，全程用动态拆解KV的逻辑，无需硬编码任何键名。

完整SQL实现 #

假设你的源表为source_table，存储JSON的字段名为json_data，SQL如下：

with parsed_raw as (
    -- 第一步：解析原始JSON为结构化类型
    select 
        json_parse(json_data) as parsed_json
    from source_table
    -- 测试示例数据可替换上面的from子句为：
    -- from (values ('{"banned":[false,true],"shadow_banned":[false,true],"profile":[{"name":"Htet AAA","phone_number":"9597927098","email":"haavai27@gmail.com"},{"name":"Htet AAA","phone_number":"9597927099","email":"haavai29@gmail.com"}],"suspend_reason":["","ManualBan"],"subscriptions":[{"welcome":true,"promotion":true,"cancellation":true,"receipt":true},{"receipt":false,"cancellation":false,"promotion":false,"welcome":false}]}')) as t(json_data)
),
top_level_kv as (
    -- 第二步：拆分顶级键值对
    select
        top_key,
        top_value
    from parsed_raw
    cross join unnest(map_entries(cast(parsed_json as map(varchar, json)))) as t(top_key, top_value)
),
base_type_diff as (
    -- 第三步：处理顶级键对应基础类型二元数组的场景
    select
        top_key as "Key (path)",
        json_extract_scalar(top_value, '$[0]') as "old value",
        json_extract_scalar(top_value, '$[1]') as "new value"
    from top_level_kv
    where json_array_length(top_value) = 2
      and not(json_typeof(json_extract(top_value, '$[0]')) = 'object' 
          and json_typeof(json_extract(top_value, '$[1]')) = 'object')
),
object_type_diff as (
    -- 第四步：处理顶级键对应两个对象数组的场景，拆分子键
    select
        concat(top_key, '#', sub_key) as "Key (path)",
        json_extract_scalar(old_obj, concat('$.', sub_key)) as "old value",
        json_extract_scalar(new_obj, concat('$.', sub_key)) as "new value"
    from (
        select
            top_key,
            json_extract(top_value, '$[0]') as old_obj,
            json_extract(top_value, '$[1]') as new_obj,
            -- 合并两个对象的所有键，避免单边存在的键遗漏
            map_keys(cast(old_obj as map(varchar, json))) 
            union 
            map_keys(cast(new_obj as map(varchar, json))) as all_sub_keys
        from top_level_kv
        where json_array_length(top_value) = 2
          and json_typeof(json_extract(top_value, '$[0]')) = 'object' 
          and json_typeof(json_extract(top_value, '$[1]')) = 'object'
    ) t
    cross join unnest(all_sub_keys) as s(sub_key)
)
-- 合并两类对比结果
select * from base_type_diff
union all
select * from object_type_diff

适配说明 #

• 如果业务中存在更深层级的嵌套对象（三级、四级嵌套），可以参照object_type_diff的逻辑递归扩展即可
• 当前所有值默认转成字符串格式输出，如果需要保留原始类型，可以去掉json_extract_scalar调用，自行适配类型转换逻辑
• 如果存在数组长度不为2的异常数据，可以在top_level_kv层加过滤条件剔除异常值

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