google-bigquery - BigQuery 标准 SQL "left"嵌套数组的交叉联接

Question

我正在尝试对包含嵌套数组的记录的表运行查询。
在下面的示例中，嵌套数组称为“age”。一些记录可能有一个空数组。

我如何运行查询运行“左”交叉连接，以便即使是没有年龄元素的记录也会被输出(年龄=空的一行)/

 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

Answer 1

#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