如何使用SQL解析JSON列并将数据拆分为多行多列

核心实现分为两个步骤：

• 对SRC字段存储的JSON对象数组做行展开（也叫炸裂），单条记录包含N个数组元素就拆分为N行，拆分后保留原表Column 1、Column 2的字段值
• 从拆分得到的单个JSON对象中，分别提取column_a、column_b、coulmn_c三个属性，映射为独立输出列

按照给出的示例数据，最终输出的结构化结果如下：

不同数据库的JSON处理语法存在差异，以下是主流数据库可直接运行的实现代码：

MySQL 8.0+ 版本 #

使用原生JSON_TABLE函数实现JSON数组转多行，无需自定义UDF：

SELECT
    `Column 1` AS Column_1,
    `Column 2` AS Column_2,
    jt.column_a AS Column_A,
    jt.column_b AS COLUMN_B,
    jt.coulmn_c AS COLUMN_C
FROM 你的实际表名,
JSON_TABLE(
    SRC,
    '$[*]' COLUMNS (
        column_a VARCHAR(32) PATH '$.column_a',
        column_b VARCHAR(32) PATH '$.column_b',
        coulmn_c JSON PATH '$.coulmn_c'
    )
) AS jt;

如果需要把coulmn_c存储的字符串数组继续拆分为单个字符串值、每个值占一行，可以再嵌套一层JSON_TABLE关联即可。

PostgreSQL 版本 #

使用LATERAL关联配合数组展开函数实现：

SELECT
    "Column 1" AS Column_1,
    "Column 2" AS Column_2,
    elem->>'column_a' AS Column_A,
    elem->>'column_b' AS COLUMN_B,
    elem->'coulmn_c' AS COLUMN_C
FROM 你的实际表名,
LATERAL jsonb_array_elements(SRC::jsonb) AS elem;

如果SRC字段是json类型而非jsonb，把jsonb_array_elements替换为json_array_elements即可；需要拆分coulmn_c数组的话，再加一层LATERAL关联jsonb_array_elements_text(elem->'coulmn_c')即可。

Hive/Spark SQL 版本 #

使用LATERAL VIEW explode做数组展开，配合get_json_object提取JSON属性：

SELECT
    `Column 1` AS Column_1,
    `Column 2` AS Column_2,
    get_json_object(single_obj, '$.column_a') AS Column_A,
    get_json_object(single_obj, '$.column_b') AS COLUMN_B,
    get_json_object(single_obj, '$.coulmn_c') AS COLUMN_C
FROM 你的实际表名
LATERAL VIEW explode(SRC) t AS single_obj;

注意：提供的JSON属性存在拼写错误coulmn_c（正确拼写应为column_c），编写SQL时必须和实际存储的JSON键名完全一致，否则会提取到NULL值。

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