如何解析含JSON数组列的表并提取id=2对应的value值？

需求说明 #

现有Hive表包含普通列（col1、col2、col3）和存储JSON数组的列（假设名为json_array_col），需提取数组中id=2对应的value值，同时保留原普通列并关联结果。

示例数据 #

假设表名为your_table，数据结构如下：

col1,col2,col3,json_array_col
value1,value2,value3,"[{""id"":1,""value"":""x""},{""id"":2,""value"":""y""},{""id"":3,""value"":""blah""}]"
value4,value5,value6,"[{""id"":1,""value"":""a""},{""id"":2,""value"":""b""},{""id"":3,""value"":""blahblah""}]"

实现SQL #

SELECT 
    col1,
    col2,
    col3,
    json_value AS id2value
FROM your_table
LATERAL VIEW explode(split(regexp_replace(json_array_col, '\\[|\\]', ''), '},\\s*\\{')) exploded_json AS json_element
LATERAL VIEW json_tuple(json_element, 'id', 'value') jt AS json_id, json_value
WHERE json_id = '2'

分步解释 #

• 清理数组格式：用regexp_replace(json_array_col, '\\[|\\]', '')去除JSON数组的首尾方括号，将[{"id":1,...},{"id":2,...}]转换为{"id":1,...},{"id":2,...}。
• 拆分JSON元素：通过split(..., '},\\s*\\{')按元素间的分隔符拆分字符串，得到单个JSON对象组成的数组。
• 展开数组为行：LATERAL VIEW explode(...)将数组中的每个JSON对象展开为单独的行，此时原表的每一行会对应多行（数组有多少元素就展开多少行），同时保留原普通列的内容。
• 提取JSON字段：LATERAL VIEW json_tuple(...)从单个JSON对象中提取id和value，并赋予明确别名，方便后续过滤和查询。
• 筛选目标数据：通过WHERE json_id = '2'过滤出id为2的行，最终得到对应value并关联原普通列。

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