BigQuery中如何过滤STRUCT数组中的空值？

在BigQuery中处理包含null元素的JSON数组时，执行SQL报错：

Array cannot have a null element; error in writing field permissions.p.accts; error in writing field permissions.p; error in writing field permissions

问题场景是当JSON中accts字段为[null]时，使用JSON_VALUE_ARRAY提取数组会触发上述错误。ARRAY_AGG的IGNORE NULLS仅适用于标量数组，无法解决STRUCT数组内的元素null问题。

可正常运行的查询：

with example as (
  select JSON '{"permissions":{"p":[{"accts":["abc"],"perms":["def"]}]}}' as json_data
)

select STRUCT( 
          ARRAY(SELECT AS STRUCT 
                  JSON_VALUE_ARRAY(permission,'$.accts') as accts,
                  JSON_VALUE_ARRAY(permission,'$.perms') as perms
                FROM UNNEST(
                            JSON_QUERY_ARRAY(example.json_data, '$.permissions.p')
                          ) as permission
                ) as p 
             ) as permissions
from example;

触发错误的查询（仅将"accts":["abc"]改为"accts":[null]）：

with example as (
  select JSON '{"permissions":{"p":[{"accts":[null],"perms":["def"]}]}}' as json_data
)

select STRUCT( 
          ARRAY(SELECT AS STRUCT 
                  JSON_VALUE_ARRAY(permission,'$.accts') as accts,
                  JSON_VALUE_ARRAY(permission,'$.perms') as perms
                FROM UNNEST(
                            JSON_QUERY_ARRAY(example.json_data, '$.permissions.p')
                          ) as permission
                ) as p 
             ) as permissions
from example;

方案1：将数组中的null替换为空字符串 #

通过UNNEST数组后用IFNULL替换null元素，再重新聚合为数组：

with example as (
  select JSON '{"permissions":{"p":[{"accts":[null],"perms":["def"]}]}}' as json_data
)

select STRUCT( 
          ARRAY(SELECT AS STRUCT 
                  -- 替换数组中的null为空字符串
                  ARRAY(SELECT IFNULL(acct, '') FROM UNNEST(JSON_VALUE_ARRAY(permission,'$.accts')) acct) as accts,
                  JSON_VALUE_ARRAY(permission,'$.perms') as perms
                FROM UNNEST(JSON_QUERY_ARRAY(example.json_data, '$.permissions.p')) as permission
                ) as p 
             ) as permissions
from example;

方案2：显式声明允许null的数组类型 #

BigQuery默认JSON_VALUE_ARRAY返回STRING[]（不允许null元素），可以显式指定为ARRAY<STRING?>（允许null元素）：

with example as (
  select JSON '{"permissions":{"p":[{"accts":[null],"perms":["def"]}]}}' as json_data
)

select STRUCT( 
          ARRAY(SELECT AS STRUCT 
                  JSON_VALUE_ARRAY(permission,'$.accts') AS accts ARRAY<STRING?>,
                  JSON_VALUE_ARRAY(permission,'$.perms') as perms
                FROM UNNEST(JSON_QUERY_ARRAY(example.json_data, '$.permissions.p')) as permission
                ) as p 
             ) as permissions
from example;

说明：方案2会保留原数组中的null元素，而方案1会将null转为空字符串，可根据业务需求选择。

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