BigQuery筛选含指定元素数组列时空数组匹配结果异常问题

场景复现 #

存在两表关联生成的结果表，包含两个数组类型字段app1、app2，示例数据：

row app1       app2
1   [app,app1] []
2   []         [app]
3   [app1]     [app]
4   []         []

需求为筛选出app1或app2数组中包含元素'app'的行，预期返回第1、2、3行。
原有查询语句仅能返回第3行，当单侧数组为空时匹配失效，原语句如下：

select * from table
where exists(SELECT * FROM UNNEST(array(select app1 from table)) AS s, 
UNNEST(array(select app2 from table)) AS d WHERE regexp_contains(format('%t',(s,d)), 
'app'))

原语句问题说明 #

• 子查询中重新对全表的app1、app2字段做UNNEST后交叉连接，未和外层当前行做关联，逻辑上是判断全表是否存在匹配值，而非判断当前行是否符合条件
• 两个数组做隐式交叉连接时，只要任意一个数组为空，连接结果就为空，因此单侧数组含目标值的行无法被命中
• 用正则匹配格式化后的字符串存在误匹配风险，若存在名称包含app的其他元素（如app2、myapp）会被错误命中

正确写法 #

直接使用BigQuery原生的数组成员判断语法即可，逻辑简洁且执行效率更高：

SELECT * FROM your_table
WHERE
  'app' IN UNNEST(app1)
  OR 'app' IN UNNEST(app2)

如果字段可能存在NULL值（注意是NULL而非空数组[]），可以加空值兜底避免返回异常：

SELECT * FROM your_table
WHERE
  IFNULL('app' IN UNNEST(app1), FALSE)
  OR IFNULL('app' IN UNNEST(app2), FALSE)

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