Azure Databricks中如何使用SQL展开嵌套数组

问题描述 #

我需要编写SQL查询来访问嵌套在另一数组中的数组内的值。我的表结构如下：

SELECT *
FROM my_table

查询返回的表结构：

期望得到扁平化的结果，包含Column_A和键为"first"的值，结果如下：

我尝试使用Lateral View Explode：

SELECT Column_A, B.SubB
FROM my_table
Lateral View Explode(Column_B) AS B

但深入到下一层时，用子查询变得非常困难。请问查询嵌套数组的最佳方式是什么？

附表创建代码：

WITH sample1 AS (
SELECT "Cell_1" AS Column_A,
       array(NAMED_STRUCT('SubB', array(NAMED_STRUCT('first', 1,
                          'second', 2))
                          ),
             NAMED_STRUCT('SubB', array(NAMED_STRUCT('first', 3,
                          'second', 4))
                          )
             )            
           AS Column_B
),
sample2 AS (
SELECT "Cell_2" AS Column_A,
       array(NAMED_STRUCT('SubB', array(NAMED_STRUCT('first', 5,
                          'second', 6))
                          )
             )            
           AS Column_B
  ),

my_table AS (SELECT *
           FROM sample1
           UNION ALL (SELECT *
           FROM sample2))

SELECT * 
FROM my_table

解决方案 #

可以**多次使用Lateral View Explode**逐层展开嵌套数组，每一层对应一个Lateral View，这种方式比子查询更直观易维护。

针对你的场景，具体查询语句如下：

SELECT 
  Column_A,
  sub.first
FROM my_table
-- 第一层：展开Column_B外层数组，得到每个含SubB的结构体
LATERAL VIEW EXPLODE(Column_B) AS b_item
-- 第二层：展开每个结构体中的SubB数组
LATERAL VIEW EXPLODE(b_item.SubB) AS sub

执行逻辑 #

1. 第一个LATERAL VIEW EXPLODE(Column_B)将Column_B的外层数组展开，每一行对应原数组中的一个{"SubB": [...]}结构体，命名为b_item；
2. 第二个LATERAL VIEW EXPLODE(b_item.SubB)将每个b_item内的SubB数组展开，每一行对应一个包含first和second的结构体，命名为sub；
3. 直接选取Column_A和sub.first即可得到目标扁平化结果。

验证结果 #

执行上述查询后，返回结果与期望完全一致：

如果后续还有更深层级的嵌套，只需继续添加对应的LATERAL VIEW EXPLODE即可。

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