BigQuery筛选嵌套数组字段时查询失效，请求技术协助

你的查询无法正常工作的核心原因是：deliveries、modalities、campaigns均为REPEATED RECORD类型，BigQuery不支持直接跨多层REPEATED字段执行UNNEST，必须逐层展开嵌套结构后再做筛选。

以下提供两种常见场景的解决方案：

场景1：展开所有嵌套层级，获取匹配的明细记录 #

如果需要将所有嵌套层级展开，只保留与campaigns.id = 4469相关的记录，可使用逐层UNNEST的方式：

SELECT *
FROM `data`
-- 第一层：展开deliveries数组
CROSS JOIN UNNEST(deliveries) AS delivery
-- 第二层：展开当前delivery下的modalities数组
CROSS JOIN UNNEST(delivery.modalities) AS modality
-- 第三层：展开当前modality下的campaigns数组
CROSS JOIN UNNEST(modality.campaigns) AS campaign
-- 筛选目标campaign id
WHERE campaign.id = 4469

场景2：保留原始嵌套结构，仅筛选包含目标id的主表行 #

如果希望保留原始表的嵌套结构，只筛选出至少存在一个campaigns.id = 4469的主表行，可使用EXISTS子查询：

SELECT *
FROM `data`
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(deliveries) AS delivery
  CROSS JOIN UNNEST(delivery.modalities) AS modality
  CROSS JOIN UNNEST(modality.campaigns) AS campaign
  WHERE campaign.id = 4469
)

进阶：保留嵌套结构并仅保留匹配的嵌套项 #

如果需要在保留主表结构的同时，只保留嵌套层级中符合条件的campaigns（及其上层的modalities、deliveries），可以用嵌套的ARRAY构造实现：

SELECT
  -- 保留主表其他字段
  * EXCEPT(deliveries),
  -- 构造过滤后的deliveries数组
  ARRAY(
    SELECT AS STRUCT
      delivery.items,
      -- 构造过滤后的modalities数组
      ARRAY(
        SELECT AS STRUCT
          modality.* EXCEPT(campaigns),
          -- 构造仅包含目标id的campaigns数组
          ARRAY(
            SELECT *
            FROM UNNEST(modality.campaigns) AS campaign
            WHERE campaign.id = 4469
          ) AS campaigns
        FROM UNNEST(delivery.modalities) AS modality
        -- 仅保留包含目标campaign的modality
        WHERE EXISTS (
          SELECT 1
          FROM UNNEST(modality.campaigns) AS campaign
          WHERE campaign.id = 4469
        )
      ) AS modalities
    FROM UNNEST(deliveries) AS delivery
    -- 仅保留包含目标campaign的delivery
    WHERE EXISTS (
      SELECT 1
      FROM UNNEST(delivery.modalities) AS modality
      CROSS JOIN UNNEST(modality.campaigns) AS campaign
      WHERE campaign.id = 4469
    )
  ) AS deliveries
FROM `data`
-- 筛选至少存在一个目标campaign的主表行
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(deliveries) AS delivery
  CROSS JOIN UNNEST(delivery.modalities) AS modality
  CROSS JOIN UNNEST(modality.campaigns) AS campaign
  WHERE campaign.id = 4469
)

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