Presto查询JSON数组元素索引并匹配对应值数组元素的实现

核心前提说明 #

原始JSON列中key-array、value-array字段存储的是转义后的数组字符串，而非原生JSON数组，解析时需要先完成两层转换：先从外层JSON提取字段值，再将字符串形式的数组反序列化为Presto原生数组类型。
以下示例假设原始表名为your_table，存储JSON数据的列名为json_col，使用时替换为实际表名、列名即可。 #

场景1：检索指定目标Key对应的Value #

如果只需要查找某个特定Key（例如AAA）对应的Value，直接通过数组定位函数实现即可：

WITH parsed_base AS (
    SELECT
        json_parse(json_extract_scalar(json_col, '$.key-array')) AS key_list,
        json_parse(json_extract_scalar(json_col, '$.value-array')) AS value_list
    FROM your_table
    -- 过滤非法数据：仅保留两个数组长度一致的记录，避免索引越界
    WHERE json_array_length(json_parse(json_extract_scalar(json_col, '$.key-array')))
        = json_array_length(json_parse(json_extract_scalar(json_col, '$.value-array')))
)
SELECT
    'AAA' AS Key,
    element_at(value_list, array_position(key_list, 'AAA')) AS Value
FROM parsed_base
-- 过滤目标Key不存在的记录
WHERE array_position(key_list, 'AAA') > 0;

场景2：拆分所有键值对输出Key、Value两列 #

如果需要把数组内所有键值对按索引对齐，输出类似AAA对应123、BBB对应456、CCC对应789的明细结果，用UNNEST带序号的方式实现：

WITH parsed_base AS (
    SELECT
        json_parse(json_extract_scalar(json_col, '$.key-array')) AS key_list,
        json_parse(json_extract_scalar(json_col, '$.value-array')) AS value_list
    FROM your_table
    WHERE json_array_length(json_parse(json_extract_scalar(json_col, '$.key-array')))
        = json_array_length(json_parse(json_extract_scalar(json_col, '$.value-array')))
)
SELECT
    single_key AS Key,
    element_at(value_list, key_idx) AS Value
FROM parsed_base
-- 展开Key数组，同时生成每个元素对应的从1开始的索引
CROSS JOIN UNNEST(key_list) WITH ORDINALITY AS t(single_key, key_idx);

关键函数说明 #

• json_extract_scalar(json, path)：从JSON中提取指定路径的标量值，这里用于取出转义后的数组字符串
• json_parse(str)：将JSON格式的字符串解析为Presto支持的原生JSON/数组类型
• array_position(arr, target)：返回目标元素在数组中第一次出现的位置，Presto数组下标从1开始，元素不存在时返回0
• element_at(arr, idx)：提取数组指定下标的元素
• UNNEST(arr) WITH ORDINALITY：展开数组的同时返回每个元素对应的索引序号，适合多数组按位置对齐的场景

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