在Databricks中用Spark SQL展开JSON数组遇null问题求助

在Databricks中用Spark SQL解析嵌套JSON时，遇到operationalOrders（数组类型）字段解析后返回Null的问题，而同层级的结构体shippingUnit可以正常解析。以下是问题分析和解决方案：

原问题代码 #

%sql
WITH data_level_1 AS (
    SELECT  
        get_json_object(`data`, '$.id') as ID,
        get_json_object(`data`, '$.shippingUnit') as shippingUnit,
        get_json_object(`data`, '$.operationalOrders') as operationalOrders
    FROM 
        orders
    WHERE 
        type LIKE '%YardOrder%'
),
data_level_2 AS (
    SELECT 
        ID,
        operationalOrders,
        shippingUnit,
        json_tuple(shippingUnit ,"id" ) as (ID_shippingUnit),
        json_tuple(operationalOrders ,"id" ) as (ID_operationalOrders)
        

    FROM 
        data_level_1
)
SELECT * FROM data_level_2

问题原因 #

get_json_object提取operationalOrders后返回的是JSON数组字符串，而json_tuple只能处理JSON对象（键值对结构），无法直接解析数组。尝试用json_tuple(operationalOrders, "id")时，因为数组本身没有id字段，所以返回Null。

解决方案 #

方案1：解析数组并展开提取所有元素的ID #

先将JSON数组字符串转为Spark的ArrayType结构体，再用explode展开数组，提取每个元素的ID：

%sql
WITH data_level_1 AS (
    SELECT  
        get_json_object(`data`, '$.id') as ID,
        get_json_object(`data`, '$.shippingUnit') as shippingUnit,
        -- 将JSON数组字符串转为Spark数组类型，schema需匹配实际结构
        from_json(
            get_json_object(`data`, '$.operationalOrders'),
            'array<struct<id:string, isActive:boolean, customerCode:string, orderId:string, cargos:array<struct<product:string, article:string>>>>'
        ) as operationalOrders
    FROM 
        orders
    WHERE 
        type LIKE '%YardOrder%'
),
data_level_2 AS (
    SELECT 
        ID,
        shippingUnit,
        json_tuple(shippingUnit, "id") as (ID_shippingUnit),
        -- 展开数组为单行记录
        explode(operationalOrders) as order_item,
        order_item.id as ID_operationalOrders
    FROM 
        data_level_1
)
SELECT ID, ID_shippingUnit, ID_operationalOrders, order_item, shippingUnit FROM data_level_2

说明：from_json的schema需要和operationalOrders数组内的对象结构完全匹配，可根据实际JSON字段调整。

方案2：直接提取数组中特定位置元素的ID #

如果operationalOrders数组只有一个元素，或仅需第一个元素的ID，可直接通过JSON路径定位：

%sql
WITH data_level_1 AS (
    SELECT  
        get_json_object(`data`, '$.id') as ID,
        get_json_object(`data`, '$.shippingUnit') as shippingUnit,
        -- 直接提取数组第一个元素的id
        get_json_object(`data`, '$.operationalOrders[0].id') as ID_operationalOrders
    FROM 
        orders
    WHERE 
        type LIKE '%YardOrder%'
),
data_level_2 AS (
    SELECT 
        ID,
        ID_operationalOrders,
        shippingUnit,
        json_tuple(shippingUnit, "id") as (ID_shippingUnit)
    FROM 
        data_level_1
)
SELECT * FROM data_level_2

说明：这种方式无需处理数组展开，适合固定长度数组的场景。

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