如何通过Spark SQL从多层嵌套结构体数组提取product_id数组？

现有表table1，Schema如下：

root
 |-- id: string (nullable = true)
 |-- info: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- info_id: string (nullable = true)
 |    |    |-- info_status: integer (nullable = true)
 |    |    |-- details: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- products: array (nullable = true)
 |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |-- product_id: string (nullable = true)
 |    |    |    |    |    |    |-- amount: integer (nullable = true)
 |    |    |    |    |    |    |-- timestamp: long (nullable = true)

需要通过纯Spark SQL提取所有product_id为数组，但执行以下SQL时出现语法错误：

select info.details.products.product_id from table1

错误信息：

org.apache.spark.sql.AnalysisException: cannot resolve 'table1.`info`.`details`['products']' due to data type mismatch: argument 2 requires integral type, however, ''products'' is of string type.

由于数据嵌套了多层数组（info、details、products均为数组类型），直接通过点路径访问会触发类型不匹配错误，以下是两种纯Spark SQL的实现方案：

方法1：逐层展开数组后聚合 #

通过LATERAL VIEW EXPLODE逐层展开嵌套数组，提取所有product_id后，按原表主键id聚合为数组：

SELECT 
    id,
    COLLECT_LIST(product_item.product_id) AS product_ids
FROM table1
LATERAL VIEW EXPLODE(info) AS info_item
LATERAL VIEW EXPLODE(info_item.details) AS detail_item
LATERAL VIEW EXPLODE(detail_item.products) AS product_item
GROUP BY id;

如果需要去重product_id，可将COLLECT_LIST替换为COLLECT_SET。

方法2：使用高阶函数扁平化嵌套数组（Spark 2.4及以上版本支持） #

利用TRANSFORM遍历每一层数组提取product_id，再通过FLATTEN将多层嵌套数组转换为一维数组，无需展开全表，性能更优：

SELECT 
    id,
    FLATTEN(
        TRANSFORM(info, info_item -> 
            FLATTEN(
                TRANSFORM(info_item.details, detail_item -> 
                    TRANSFORM(detail_item.products, product_item -> product_item.product_id)
                )
            )
        )
    ) AS product_ids
FROM table1;

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