Spark SQL嵌套字段查询：单语句获取产品最新属性方案问询

问题背景 #

有产品视图products，包含嵌套字段categories（Struct数组类型，每个Struct含name和taxonomy字段），部分属性随时间更新，需要获取每个产品的最新brand、对应taxonomy='a'的category_a、对应taxonomy='b'的category_b。原查询因Spark SQL语法限制报错：无法在子查询的EXPLODE中访问外层列。

单条查询解决方案 #

方案1：先展开分类数组，再用窗口函数取最新值 #

先通过LATERAL VIEW EXPLODE展开categories，为每条记录生成对应taxonomy的分类值，再用窗口函数按产品分组、日期排序取最后一个非空值，最后去重得到结果：

WITH expanded_products AS (
    SELECT
        product,
        date,
        brand,
        CASE WHEN cat.taxonomy = 'a' THEN cat.name END AS category_a,
        CASE WHEN cat.taxonomy = 'b' THEN cat.name END AS category_b
    FROM products
    LATERAL VIEW EXPLODE(categories) exploded AS cat
    UNION ALL
    -- 兼容categories为空的记录
    SELECT
        product,
        date,
        brand,
        NULL AS category_a,
        NULL AS category_b
    FROM products
    WHERE size(categories) = 0 OR categories IS NULL
),
latest_values AS (
    SELECT
        product,
        LAST_VALUE(brand IGNORE NULLS) OVER (
            PARTITION BY product ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS brand,
        LAST_VALUE(category_a IGNORE NULLS) OVER (
            PARTITION BY product ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS category_a,
        LAST_VALUE(category_b IGNORE NULLS) OVER (
            PARTITION BY product ORDER BY date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS category_b
    FROM expanded_products
)
SELECT DISTINCT product, brand, category_a, category_b
FROM latest_values;

方案2：使用高阶函数直接提取分类（无需展开数组） #

利用Spark高阶数组函数filter和element_at，直接从categories数组中过滤目标taxonomy的元素并提取name，再用窗口函数取最新值：

SELECT DISTINCT
    product,
    LAST_VALUE(brand IGNORE NULLS) OVER (
        PARTITION BY product ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS brand,
    LAST_VALUE(
        element_at(transform(filter(categories, cat -> cat.taxonomy = 'a'), cat -> cat.name), 1)
        IGNORE NULLS
    ) OVER (
        PARTITION BY product ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS category_a,
    LAST_VALUE(
        element_at(transform(filter(categories, cat -> cat.taxonomy = 'b'), cat -> cat.name), 1)
        IGNORE NULLS
    ) OVER (
        PARTITION BY product ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS category_b
FROM products;

方案说明 #

• 方案1逻辑直观，兼容空数组场景，适合复杂嵌套结构的处理；
• 方案2无需展开数组，性能更优，适合数组元素较少的场景。两种方案均可输出符合预期的结果。

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