nullを含むarray列をunnestする際にnullレコードを保持したいときのメモ

概要

BigQueryでarray等の列をunnestする際に、その列のnullなレコードはunnestの処理時に残らない。

nullを含む列のunnest例

with
test as (
  select
    n,
    if(mod(n, 2) != 0, generate_array(1, n), null) as x
  from
    unnest(generate_array(1, 3)) as n
)

select
  n, x, y
from
  test, unnest(x) as y

このようなケースで、nullなレコードも残す方法について。

やり方

nullレコードを残すunnest(array)

with
test as (
  select
    n,
    if(mod(n, 2) != 0, generate_array(1, n), null) as x
  from
    unnest(generate_array(1, 3)) as n
)

select
  n, x, y
from
  test, unnest(coalesce(x, [null])) as y

nullレコードを残すunnest(struct)

with
test as (
  select
    'normal' as n,
    [struct(1 as x, 'a' as y),
     struct(2 as x, 'b' as y)
    ] as v
  union all
  select
    'null' as n,
    null as v
)

select
  n, v, x, y
from
  test, unnest(if(array_length(v) > 0, v, [struct(cast(null as int), cast(null as string))]))