Presto、Hive中数组结构体数据扁平化查询方法咨询

云原生阿岚

2026-5-27

嗨，这个需求本质是把数组类型列里的每个元素拆分行展示（也叫行转列/数组展开），不同数据库的实现语法略有区别，我给你整理几个常用数据库的查询语句：

针对不同数据库的实现方案

PostgreSQL

假设你的col2是jsonb类型（如果是字符串类型，可以用col2::jsonb转换），用jsonb_array_elements函数来拆分数组：

SELECT 
    dep_id AS id,
    (emp_data->>'emp_id')::bigint AS emp_id
FROM 
    your_table_name,
    jsonb_array_elements(col2) AS emp_data
WHERE 
    dep_id = '112';

简单解释：jsonb_array_elements会把数组中的每个结构体对象拆成单独一行，然后通过->>提取emp_id的字符串值，再转成数值类型。

MySQL 8.0及以上版本

MySQL 8.0开始支持JSON_TABLE函数，能把JSON数组转换成关系表结构：

SELECT 
    t.dep_id AS id,
    j.emp_id
FROM 
    your_table_name t,
    JSON_TABLE(
        t.col2,
        '$[*]' COLUMNS (
            emp_id BIGINT PATH '$.emp_id'
        )
    ) j
WHERE 
    t.dep_id = '112';

这里JSON_TABLE会遍历数组的每个元素，提取指定路径的emp_id字段，再和原表关联获取对应的dep_id。

Hive/Spark SQL

这类大数据SQL引擎用explode结合get_json_object来实现：

SELECT 
    dep_id AS id,
    get_json_object(emp_data, '$.emp_id') AS emp_id
FROM 
    your_table_name
LATERAL VIEW explode(col2) exploded_table AS emp_data
WHERE 
    dep_id = '112';

explode负责把数组拆成单个元素行，get_json_object从每个结构体中提取emp_id的值。