如何在Hive中不使用explode，从结构体数组匹配字段获取age值

问题背景 #

现有Hive表数据如下：

fname | lname | records |
------|-------|---------|
 John |  Doe  | [{"name": "John", "lname": "Doe", "age": 55}, {"name": "Jacky", "lname": "Collins", "age": 67}] |
 Jane |  Doe  | [{"name": "John", "lname": "Doe", "age": 55}, {"name": "Jacky", "lname": "Collins", "age": 67}] |

要求不使用explode函数展开records列，根据每行的fname和lname匹配records数组中对应的JSON对象，提取其age值，预期输出：

fname | lname | age  |
------|-------|------|
 John |  Doe  | 55   |
 Jane |  Doe  | NULL |

解决方案 #

方法一：使用Hive高阶函数（Hive 2.3.0+） #

利用filter和element_at高阶函数直接筛选数组元素，代码简洁高效：

SELECT 
    fname,
    lname,
    get_json_object(
        element_at(
            filter(
                records, 
                x -> get_json_object(x, '$.name') = fname AND get_json_object(x, '$.lname') = lname
            ), 
            1
        ), 
        '$.age'
    ) AS age
FROM your_table_name;

逻辑说明

1. filter(records, x -> ...)：遍历records数组，筛选出name和lname与当前行字段完全匹配的JSON对象，返回筛选后的数组（无匹配则为空）
2. element_at(..., 1)：取出筛选后数组的第一个元素（匹配逻辑唯一，最多一个结果），空数组则返回NULL
3. get_json_object(..., '$.age')：从匹配的JSON对象中提取age值，若对象为NULL则返回NULL

方法二：兼容低版本Hive（无高阶函数支持） #

通过字符串拼接、数组匹配的方式实现，适用于Hive 2.3.0以下版本：

SELECT 
    fname,
    lname,
    CASE 
        WHEN array_contains(
            transform(records, x -> concat(get_json_object(x, '$.name'), '|', get_json_object(x, '$.lname'))),
            concat(fname, '|', lname)
        ) THEN 
            get_json_object(
                records, 
                concat('$[', 
                    find_in_set(
                        concat(fname, '|', lname),
                        concat_ws(',', transform(records, x -> concat(get_json_object(x, '$.name'), '|', get_json_object(x, '$.lname'))))
                    ) - 1,
                    '].age'
                )
            )
        ELSE NULL 
    END AS age
FROM your_table_name;

逻辑说明

1. transform(records, x -> concat(...))：将数组中每个JSON对象转换为name|lname格式的字符串，方便后续匹配
2. array_contains(...)：判断当前行的fname|lname是否存在于转换后的数组中，确认是否有匹配项
3. find_in_set(...)：找到匹配字符串在拼接后的字符串中的位置，减1得到其在原数组中的索引
4. get_json_object(records, concat('$[', index, '].age'))：根据索引定位到目标JSON对象，提取对应的age值

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