BigQuery标准SQL“左”交叉连接嵌套数组
问题描述:
我试图在包含嵌套数组的记录的表上运行查询。 在下面的例子中,嵌套数组被称为“年龄”。某些记录可能有一个空数组。BigQuery标准SQL“左”交叉连接嵌套数组
如何运行一个查询运行了“左”的交叉连接,因此,即使是在年龄没有元素的记录将被输出(一行与年龄= NULL)/
WITH people AS (
select * from unnest(
[STRUCT("Tim" as name, [5] as ages),
STRUCT("Jane" as name, [] as ages)])
)
select
name
from people, unnest(ages) as a
答
#standardSQL
WITH people AS (
SELECT * FROM UNNEST(
[STRUCT("Tim" AS name, [5] AS ages),
STRUCT("Jane" AS name, [] AS ages)])
)
SELECT
name
FROM people
LEFT JOIN UNNEST(ages) AS a