BigQuery增量物化视图报错：无法使用ARRAY函数，求SQL改写方案

BigQuery增量物化视图明确规定不能使用ARRAY函数（包括ARRAY(...)构造器和ARRAY_AGG聚合函数），这就是你遇到报错的直接原因。下面给出两种满足你需求的解决方案：

方案一：改写SQL避免ARRAY函数，创建增量物化视图 #

你的原SQL先UNNEST(document_details)再用ARRAY_AGG重新聚合数组，属于没必要的来回转换。如果原始表的document_details字段本身就符合目标Schema的数组结构，直接复用该字段就能完全去掉ARRAY相关函数：

SELECT
  '' as Status,
  Country,
  'US-Docketing' as Source,
  customer_name as CustomerName,
  customer_number as CustomerNumber,
  client_number as ClientName,
  ClientId,
  application_number as `ApplicationNo`,
  docket_queue_id as `DocketNo`,
  attorney_docket_number as `AttorneyDocketNo`,
  DateReceived as `ReceivedDate`,
  document_details as DocumentDetails
FROM `PROJECT.POC.sample_mat_view`;

如果原始document_details的结构（比如annotations字段）和目标Schema有差异，可用TRANSFORM函数转换数组元素（该函数不在增量物化视图的禁用列表中）：

SELECT
  '' as Status,
  Country,
  'US-Docketing' as Source,
  customer_name as CustomerName,
  customer_number as CustomerNumber,
  client_number as ClientName,
  ClientId,
  application_number as `ApplicationNo`,
  docket_queue_id as `DocketNo`,
  attorney_docket_number as `AttorneyDocketNo`,
  DateReceived as `ReceivedDate`,
  TRANSFORM(
    document_details,
    doc => STRUCT(
      doc.DocumentCode,
      doc.Title,
      doc.JobDocumentId,
      doc.JobDocumentSourceId,
      TRANSFORM(
        doc.annotations,
        ann => STRUCT(ann.annotationaId, ann.display_name, ann.Value, ann.source)
      ) as annotations
    )
  ) as DocumentDetails
FROM `PROJECT.POC.sample_mat_view`;

用上述SQL就能创建增量物化视图，满足低延迟、可手动刷新的需求。

方案二：用调度查询+目标表替代物化视图 #

如果因特殊原因无法创建增量物化视图，可通过调度查询定期刷新目标表，同时支持手动触发刷新，完全保留原Schema：

1. 先创建目标表，结构与原SQL输出一致：

CREATE TABLE `PROJECT.POC.target_table`
AS
SELECT
  '' as Status,
  Country,
  'US-Docketing' as Source,
  customer_name as CustomerName,
  customer_number as CustomerNumber,
  client_number as ClientName,
  ClientId,
  application_number as `ApplicationNo`,
  docket_queue_id as `DocketNo`,
  attorney_docket_number as `AttorneyDocketNo`,
  DateReceived as `ReceivedDate`,
  ARRAY_AGG(
    STRUCT(
      document_details_unnest.DocumentCode,
      document_details_unnest.Title,
      document_details_unnest.JobDocumentId,
      document_details_unnest.JobDocumentSourceId,
      ARRAY(SELECT AS STRUCT
               annotationaId,
               display_name,
               Value,
               source
             FROM UNNEST(document_details_unnest.annotations)) as annotations
          )
  ) as DocumentDetails
FROM `PROJECT.POC.sample_mat_view`, UNNEST(document_details) as document_details_unnest
GROUP BY 1,2,3,4,5,6,7,8,9,10,11;

2. 创建调度查询，设置刷新频率（比如每5分钟），如果基础表有时间戳字段，可采用增量更新逻辑：

-- 增量更新（假设基础表有last_updated时间戳字段）
MERGE `PROJECT.POC.target_table` t
USING (
  SELECT
    '' as Status,
    Country,
    'US-Docketing' as Source,
    customer_name as CustomerName,
    customer_number as CustomerNumber,
    client_number as ClientName,
    ClientId,
    application_number as `ApplicationNo`,
    docket_queue_id as `DocketNo`,
    attorney_docket_number as `AttorneyDocketNo`,
    DateReceived as `ReceivedDate`,
    ARRAY_AGG(
      STRUCT(
        document_details_unnest.DocumentCode,
        document_details_unnest.Title,
        document_details_unnest.JobDocumentId,
        document_details_unnest.JobDocumentSourceId,
        ARRAY(SELECT AS STRUCT
                 annotationaId,
                 display_name,
                 Value,
                 source
               FROM UNNEST(document_details_unnest.annotations)) as annotations
            )
    ) as DocumentDetails
  FROM `PROJECT.POC.sample_mat_view`, UNNEST(document_details) as document_details_unnest
  WHERE last_updated > (SELECT MAX(last_updated) FROM `PROJECT.POC.target_table`)
  GROUP BY 1,2,3,4,5,6,7,8,9,10,11
) s
ON t.ClientId = s.ClientId AND t.`ApplicationNo` = s.`ApplicationNo`
WHEN MATCHED THEN UPDATE SET
  DocumentDetails = s.DocumentDetails,
  ReceivedDate = s.ReceivedDate
WHEN NOT MATCHED THEN INSERT ROW;

如果基础表没有时间戳字段，可采用全量刷新（直接覆盖目标表），调度查询逻辑为：

TRUNCATE TABLE `PROJECT.POC.target_table`;
INSERT INTO `PROJECT.POC.target_table`
SELECT
  '' as Status,
  Country,
  'US-Docketing' as Source,
  customer_name as CustomerName,
  customer_number as CustomerNumber,
  client_number as ClientName,
  ClientId,
  application_number as `ApplicationNo`,
  docket_queue_id as `DocketNo`,
  attorney_docket_number as `AttorneyDocketNo`,
  DateReceived as `ReceivedDate`,
  ARRAY_AGG(
    STRUCT(
      document_details_unnest.DocumentCode,
      document_details_unnest.Title,
      document_details_unnest.JobDocumentId,
      document_details_unnest.JobDocumentSourceId,
      ARRAY(SELECT AS STRUCT
               annotationaId,
               display_name,
               Value,
               source
             FROM UNNEST(document_details_unnest.annotations)) as annotations
          )
  ) as DocumentDetails
FROM `PROJECT.POC.sample_mat_view`, UNNEST(document_details) as document_details_unnest
GROUP BY 1,2,3,4,5,6,7,8,9,10,11;

这种方式支持手动触发刷新，延迟可通过调度频率灵活调整。

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