BigQuery中嵌套表结合Array与Where子句查询出现多余空行问题

首先确认：你创建的确实是嵌套表——BigQuery中包含数组类型字段的表就属于嵌套表，是半结构化数据的典型存储方式。

为什么原查询返回所有部门（包括空数组行）？ #

你的原查询是直接从EmployeeTable全表读取所有部门，内层的ARRAY(...)只是对每个部门的name数组做过滤，生成仅包含enes的子数组，但没有对部门本身做过滤。哪怕某个部门里没有enes，这个部门依然会被返回，只是对应的EmpNameDisplay是空数组。如果表中有上百万个部门，确实会全部返回，因为外层查询没有WHERE条件限制。

而你改用非嵌套表时，表结构是每行对应一个员工，用WHERE employeeName='enes'直接过滤掉了所有不匹配的行，自然只返回enes所在的部门——这两种表的查询逻辑本质不同：非嵌套表是行级过滤，嵌套表如果只处理数组内容，不会影响外层的行选择。

正确的查询写法 #

要只返回enes所在的部门，有两种常用思路：

思路1：用EXISTS过滤部门后再生成数组

SELECT
  dept,
  ARRAY(SELECT employeeName FROM UNNEST(name) AS employeeName
        WHERE employeeName LIKE 'enes' AND employeeName IS NOT NULL) AS EmpNameDisplay
FROM EmployeeTable
WHERE EXISTS (
  SELECT 1 FROM UNNEST(name) AS employeeName
  WHERE employeeName LIKE 'enes' AND employeeName IS NOT NULL
)

外层的EXISTS子句会检查当前部门的name数组中是否存在符合条件的员工，只有满足的部门才会被选中，避免返回空数组的部门。

思路2：先展开数组过滤，再重新聚合

SELECT
  dept,
  ARRAY_AGG(employeeName) AS EmpNameDisplay
FROM EmployeeTable, UNNEST(name) AS employeeName
WHERE employeeName LIKE 'enes' AND employeeName IS NOT NULL
GROUP BY dept

这种方式先把嵌套数组展开成扁平的员工行，过滤出enes的记录后，再按部门重新聚合为数组，结果只会包含有enes的部门。

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