如何在BigQuery中生成单手机号对应全商品名的嵌套JSON字符串

你当前的查询因未做分组聚合，导致每个关联的product_name都会生成一行重复手机号。要实现单手机号对应一行且包含所有关联产品名的嵌套JSON，需通过分组聚合+数组收集实现，具体修改如下：

修改后的查询语句 #

SELECT 
  cus.phone,
  TO_JSON_STRING(STRUCT(
    ARRAY_AGG(line.product_name IGNORE NULLS) AS product_names
  )) AS attributes
FROM `dtm_med.t1_customer` cus
LEFT JOIN `dtm_med.t2_toa_total_sales_line` line ON cus.phone = line.phone
LEFT JOIN `med_product.raw_cms_users` u ON u.id = line.patient_id
WHERE 
  DATE_DIFF(CURRENT_DATE(), DATE(latest_order_date), WEEK) < 26
  AND sale_contribution > 3000000 
  AND transaction_count > 2 
GROUP BY cus.phone

关键说明 #

• GROUP BY cus.phone：强制按手机号分组，确保每个手机号仅输出一行
• ARRAY_AGG(line.product_name IGNORE NULLS)：将当前手机号关联的所有product_name收集为数组，IGNORE NULLS可过滤关联不到产品时的空值
• STRUCT(...) + TO_JSON_STRING：把数组包装为指定结构的JSON字符串，最终attributes字段输出格式类似{"product_names": ["产品A", "产品B"]}

内容的提问来源于stack exchange，提问作者Minh Nguyễn Nhật