BigQuery中多数组合并为单数组的实现方案咨询

问题分析 #

你当前用string_agg把每行生成的JSON数组字符串直接拼接，导致输出多个独立数组。正确逻辑应该是先把每行的操作元素（ping/call）拆成单独记录，再聚合所有元素为一个数组，最后转成JSON格式。

解决方案 #

用UNNEST展开每行的操作元素数组，再通过ARRAY_AGG聚合所有元素为单个数组，最后用TO_JSON_STRING转成JSON字符串：

SELECT 
  id, 
  name,
  TO_JSON_STRING(ARRAY_AGG(elem)) AS chnl
FROM (
  SELECT 
    id, 
    name,
    -- 将每行的ping和call转为数组元素并展开
    UNNEST([
      STRUCT(recent_ping AS name, date_ts AS date),
      STRUCT(recent_call AS name, date_ts AS date)
    ]) AS elem
  FROM `pz-dev-it-0.dt_cust_tbls.ort_stg`
  -- 可选：过滤ping和call都为null的无操作行，需保留null则删除该条件
  WHERE recent_ping IS NOT NULL OR recent_call IS NOT NULL
)
GROUP BY id, name

效果说明 #

• 内层查询通过UNNEST把每行的两个操作元素拆成独立行
• 外层按id和name分组，用ARRAY_AGG将所有操作元素聚合成一个大数组
• 最终用TO_JSON_STRING把数组转成符合预期的JSON格式，输出单个包含所有操作元素的数组

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