在GBQ（SQL）中拼接不同长度的结构体/数组

要实现将两个长度不同的数组按位置配对（短数组补NULL），核心思路是先将数组拆分为带索引的行，再按索引关联，最后重新聚合为目标数组。以下是具体实现步骤：

步骤1：为两个表的数组元素添加索引 #

先分别对两个聚合后的表进行UNNEST操作，同时用OFFSET()获取每个元素在数组中的位置索引：

处理数值表（第一个表） #

WITH value_table AS (
  -- 原第一个表的聚合逻辑
  WITH source_data AS (
    SELECT 'MMTT' AS client_id, CAST('2022-10-13' AS DATE) AS session_date, 1 AS value UNION ALL
    SELECT 'MMTT', CAST('2022-10-13' AS DATE), 2 UNION ALL
    SELECT 'MMTT', CAST('2022-10-13' AS DATE), 3 UNION ALL
    SELECT 'MMTT', CAST('2022-10-14' AS DATE), 1 UNION ALL
    SELECT 'MMTT', CAST('2022-10-14' AS DATE), 2 UNION ALL
    SELECT 'MMTT', CAST('2022-10-14' AS DATE), 3
  )
  SELECT 
    client_id, 
    session_date, 
    ARRAY_AGG(STRUCT(value)) AS value_info
  FROM source_data
  GROUP BY 1,2
),
-- 拆分数组并添加索引
value_with_index AS (
  SELECT
    client_id,
    session_date,
    elem.value,
    idx AS pos  -- 获取元素在数组中的位置
  FROM value_table, UNNEST(value_info) AS elem WITH OFFSET idx
)

处理语言表（第二个表） #

, language_table AS (
  -- 原第二个表的聚合逻辑
  WITH source_data AS (
    SELECT 'MMTT' AS client_id, CAST('2022-10-13' AS DATE) AS session_date, 'French' AS languages UNION ALL
    SELECT 'MMTT', CAST('2022-10-13' AS DATE), 'English' UNION ALL
    SELECT 'MMTT', CAST('2022-10-14' AS DATE), 'French' UNION ALL
    SELECT 'MMTT', CAST('2022-10-14' AS DATE), 'English'
  )
  SELECT 
    client_id, 
    session_date, 
    ARRAY_AGG(STRUCT(languages)) AS languages_info
  FROM source_data
  GROUP BY 1,2
),
-- 拆分数组并添加索引
language_with_index AS (
  SELECT
    client_id,
    session_date,
    elem.languages,
    idx AS pos
  FROM language_table, UNNEST(languages_info) AS elem WITH OFFSET idx
)

步骤2：按索引关联并重新聚合 #

使用FULL JOIN确保两个表的所有索引都被保留（短数组的缺失索引会补NULL），然后按client_id、session_date分组，重新聚合为目标数组：

SELECT
  COALESCE(v.client_id, l.client_id) AS client_id,
  COALESCE(v.session_date, l.session_date) AS session_date,
  ARRAY_AGG(STRUCT(v.value, l.languages) ORDER BY COALESCE(v.pos, l.pos)) AS combined_info
FROM value_with_index v
FULL JOIN language_with_index l
  ON v.client_id = l.client_id
  AND v.session_date = l.session_date
  AND v.pos = l.pos
GROUP BY 1,2

说明 #

• UNNEST ... WITH OFFSET：将数组拆分为每行一个元素，同时获取元素在原数组中的位置索引，这是实现按位置配对的核心。
• FULL JOIN：保证两个表中所有位置的元素都被关联，短数组中没有对应位置的元素会填充NULL。
• ARRAY_AGG ... ORDER BY pos：重新聚合时按索引排序，确保元素顺序与原数组一致。

执行上述SQL后，即可得到预期的输出结构：每个元素包含对应位置的value和languages，短数组的缺失位置补NULL。

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