使用HIVE Query解析JSON数组并生成结构化表

需求 #

HIVE表jt的每条记录是一个JSON数组，需解析成包含以下字段的结构化表：

• monthly_invest：对应attributeId = 1的value值
• execute_date：对应attributeId = 2的value值
• deposit_term：对应attributeId = 3的value值
• init_investment：对应attributeId = 4的value值
• investment_period：对应attributeId = 5的value值
• retirement_fund：对应attributeId = 6的value值
• investment_fund：对应attributeId = 7的value值

示例JSON数组：

[{"col": 1, "row": 1, "code": "INVESTMENT_PERIOD", "name": "Thời gian đầu tư", "unit": "Năm", "order": 1, "value": 10, "dataType": "INTEGER", "htmlType": "INPUT", "targetId": 1, "attributeId": 5}, {"col": 2, "row": 1, "code": "RETIREMENT_FUND", "name": "Số tiền cần có tại thời điểm nghỉ hưu", "unit": "VND", "order": 2, "value": 10000000, "dataType": "FLOAT", "htmlType": "INPUT", "targetId": 1, "attributeId": 6}, {"col": 1, "row": 2, "code": "INIT_INVESTMENT", "name": "Số tiền đầu tư ban đầu", "unit": "VND", "order": 3, "value": 10000, "dataType": "FLOAT", "htmlType": "INPUT", "targetId": 1, "attributeId": 4}, {"col": 2, "row": 2, "code": "MONTHLY_INVEST", "name": "Số tiền đầu tư định kỳ", "unit": "VND", "order": 4, "value": 10000, "dataType": "FLOAT", "htmlType": "INPUT", "targetId": 1, "attributeId": 1}, {"col": 1, "row": 3, "code": "DEPOSIT_TERM", "name": "Ngày nộp tiền định kỳ", "unit": "", "order": 5, "value": 15, "dataType": "INTEGER", "htmlType": "SELECT", "targetId": 1, "attributeId": 3}]

解决方案 #

使用from_json解析数组结构，lateral view explode拆分数组元素，再通过条件聚合提取对应字段：

SELECT
    MAX(CASE WHEN item.attributeId = 1 THEN item.value END) AS monthly_invest,
    MAX(CASE WHEN item.attributeId = 2 THEN item.value END) AS execute_date,
    MAX(CASE WHEN item.attributeId = 3 THEN item.value END) AS deposit_term,
    MAX(CASE WHEN item.attributeId = 4 THEN item.value END) AS init_investment,
    MAX(CASE WHEN item.attributeId = 5 THEN item.value END) AS investment_period,
    MAX(CASE WHEN item.attributeId = 6 THEN item.value END) AS retirement_fund,
    MAX(CASE WHEN item.attributeId = 7 THEN item.value END) AS investment_fund
FROM
    jt
    LATERAL VIEW explode(
        from_json(
            jt.json_array_col, -- 替换为表中存储JSON数组的字段名
            'array<struct<attributeId:int,value:string>>'
        )
    ) exploded AS item
-- 按原表唯一标识分组，确保原每条记录对应结果一行
GROUP BY
    jt.record_id -- 替换为表中的唯一主键字段，若无则根据实际情况调整分组逻辑
;

关键说明 #

• from_json需指定匹配数据的结构体类型：示例中attributeId设为int，value设为string，可根据实际数据类型（如float、date）调整。
• lateral view explode将数组拆分为多行，每个数组元素对应一行数据。
• MAX结合CASE WHEN实现同一原记录的多属性值聚合，无匹配attributeId的字段会返回NULL。
• 必须按原表唯一标识分组，避免不同记录的属性值被错误聚合。

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