Azure Databricks查询结构体数组列返回重复值问题排查

问题背景 #

现有Azure Databricks表finlog，结构如下：

CREATE TABLE finlog 
(
    AccountNumber       STRING,
    ClientControlNumber STRING,
    LogDetails          ARRAY<STRUCT<DetailId: STRING, ControlId: STRING, Amount: STRING, Label: STRING>>,
    OriginSource        STRING
);

需要查询符合以下条件的记录：

• LogDetails数组非空
• 数组中存在结构体，其ControlId与当前记录的ClientControlNumber匹配，且Label为ACTIVE
  合格记录返回匹配结构体的Amount（命名为LogDetailAmount），不合格记录返回0.00。

原查询语句返回所有行的LogDetailAmount均为-0.95，原因是子查询未与当前行关联，导致结果错误。

错误分析 #

原SQL中的子查询直接从finlog表全表查询，未与外层的当前行做关联：

-- 错误：子查询的finlog AS fl是独立的表查询，不是外层的行
WHEN EXISTS (SELECT 1 FROM finlog AS fl ...)

这会导致EXISTS判断的是全表是否存在符合条件的记录，而非当前行；后续的子查询也会返回全表中任意符合条件的Amount，最终所有行都取同一个值。

正确解法 #

方法1：使用数组函数（高效推荐） #

利用Spark SQL的数组函数直接操作，无需展开数组：

SELECT
    CASE
        -- 筛选出符合条件的结构体数组，判断是否存在
        WHEN size(filter(LogDetails, x -> x.ControlId = ClientControlNumber AND x.Label = 'ACTIVE')) > 0
        -- 取第一个符合条件的结构体的Amount，转换为小数类型
        THEN CAST(element_at(filter(LogDetails, x -> x.ControlId = ClientControlNumber AND x.Label = 'ACTIVE'), 1).Amount AS DECIMAL(18,2))
        ELSE 0.00
    END AS LogDetailAmount
FROM finlog;

• filter函数：从LogDetails数组中筛选出满足ControlId匹配且Label='ACTIVE'的结构体
• size函数：判断筛选后的数组是否非空
• element_at函数：取筛选后数组的第一个元素，提取其Amount并转换为小数类型

方法2：使用LATERAL VIEW EXPLODE展开数组 #

通过展开数组后筛选聚合，适合需要对数组元素做更多处理的场景：

SELECT
    -- 取符合条件的Amount，无则返回0.00
    COALESCE(MAX(CASE 
                    WHEN ld.ControlId = fl.ClientControlNumber AND ld.Label = 'ACTIVE' 
                    THEN CAST(ld.Amount AS DECIMAL(18,2)) 
                 END), 0.00) AS LogDetailAmount
FROM finlog AS fl
-- 外连接展开LogDetails数组，保留原表所有行
LEFT JOIN LATERAL VIEW OUTER EXPLODE(fl.LogDetails) exploded AS ld ON TRUE
-- 按原表的所有字段分组，确保回到原表的行粒度
GROUP BY fl.AccountNumber, fl.ClientControlNumber, fl.OriginSource, fl.LogDetails;

• LATERAL VIEW OUTER EXPLODE：将数组展开为多行，OUTER确保原表中数组为空的行也被保留
• CASE语句筛选符合条件的Amount，MAX聚合确保每个原行只取一个符合条件的值（若有多个）
• COALESCE处理无符合条件的情况，返回0.00

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