如何用Standard SQL在GBQ中向STRUCT数组追加新行

问题背景 #

我有两张BigQuery表，第一张表（记为table1）的数据结构和生成SQL如下：

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2022-10-13' AS DATE) AS session_date,
    'denied' AS value
  UNION ALL
  SELECT
    1,
    CAST('2022-10-15' AS DATE),
    'granted'
  UNION ALL
  SELECT
    1,
    CAST('2022-10-18' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-01' AS DATE),
    'denied'
  UNION ALL
  SELECT
    2,
    CAST('2022-01-05' AS DATE),
    'granted'
  UNION ALL
  SELECT
    3,
    CAST('2022-01-01' AS DATE),
    'granted'
  UNION ALL
  SELECT
    4,
    CAST('2022-01-03' AS DATE),
    'granted'),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date, value) ORDER BY session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

表中每个client_id对应一个push_permission数组，存储该客户的权限变更记录。

另一张表（记为table2）包含客户1和客户2的新权限记录，生成SQL如下：

WITH
  source_data AS (
  SELECT
    1 AS client_id,
    CAST('2023-05-08' AS DATE) AS session_date,
    'denied' AS value
  UNION ALL
  SELECT
    2,
    CAST('2023-05-08' AS DATE),
    'granted'
  ),
  max_date AS (
  SELECT
    client_id,
    session_date,
    value
  FROM
    source_data )
SELECT
  client_id,
  MAX(session_date) AS last_activity,
  ARRAY_AGG(STRUCT(session_date, value) ORDER BY session_date) AS push_permission
FROM
  max_date
GROUP BY
  1

我需要将table2中的新记录追加到table1对应客户的push_permission数组中，同时更新last_activity为最新日期。之前尝试INSERT会新增行而非追加数组，UPDATE的写法也不正确，尝试的代码如下：

UPDATE
  `table1`
SET
  push_permission = ARRAY(
  SELECT
    push_permission
  FROM
    UNNEST(push_permission) AS push_permission
  UNION ALL
  SELECT
    (CAST(CURRENT_DATE()-1 AS DATE),
      push_permission.push_system_permission ))
  WHERE client_id IN (SELECT
    DISTINCT(client_id)
  FROM
    `table2`)

请问在BigQuery中如何实现这个需求？

解决方案 #

基础实现方案 #

可以使用BigQuery的ARRAY_CONCAT函数合并数组，同时用GREATEST更新最新活动日期，具体SQL如下：

UPDATE `table1` t1
SET
  push_permission = ARRAY_CONCAT(
    t1.push_permission,
    (SELECT t2.push_permission FROM `table2` t2 WHERE t2.client_id = t1.client_id)
  ),
  last_activity = GREATEST(
    t1.last_activity,
    (SELECT t2.last_activity FROM `table2` t2 WHERE t2.client_id = t1.client_id)
  )
WHERE EXISTS (
  SELECT 1 FROM `table2` t2 WHERE t2.client_id = t1.client_id
)

代码说明 #

• ARRAY_CONCAT：将table1原有的权限记录数组与table2中对应客户的新记录数组合并，直接实现追加效果。
• GREATEST：对比原表和新表的最新活动日期，取较大值作为更新后的last_activity。
• WHERE EXISTS：仅更新在table2中存在的客户，避免对无新记录的客户执行无效操作。

兼容多新记录的场景 #

如果table2中同一个客户有多条新记录，建议先对table2按客户聚合，确保每个客户的新记录是有序合并后的数组，再执行更新：

WITH aggregated_table2 AS (
  SELECT
    client_id,
    MAX(session_date) AS last_activity,
    ARRAY_AGG(STRUCT(session_date, value) ORDER BY session_date) AS push_permission
  FROM `table2`
  GROUP BY client_id
)
UPDATE `table1` t1
SET
  push_permission = ARRAY_CONCAT(t1.push_permission, t2.push_permission),
  last_activity = GREATEST(t1.last_activity, t2.last_activity)
FROM aggregated_table2 t2
WHERE t1.client_id = t2.client_id

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