BigQuery中如何将5列手机号合并为去空去重的单字符串/JSON字段

方案1：生成标准JSON数组字符串（适配JSON_EXTRACT_SCALAR提取需求） #

这是最符合你需求的方案，输出为合法JSON字符串，可直接用JSON_EXTRACT_SCALAR提取指定位置的手机号，不同SQL引擎修改最后一步聚合逻辑即可：

• BigQuery/Spark SQL：将最后array_agg(phone)替换为to_json_string(array_agg(phone))
• MySQL 5.7+/8.0：将最后array_agg(phone)替换为json_arrayagg(phone)

修改后的完整代码示例（适配BigQuery）：

WITH phones as (
SELECT '6446177' as id, NULL as phone1,NULL as phone2,11572946 as phone3,NULL as phone4,11572946 as phone5 
UNION ALL
SELECT '8523122' as id, NULL as phone1,NULL as phone2,29703165 as phone3,NULL as phone4,29703165 as phone5 
UNION ALL
SELECT '5606494' as id, NULL as phone1,NULL as phone2,51156520 as phone3,NULL as phone4,32247153 as phone5
UNION ALL
SELECT '6560607' as id, 85137 as phone1,NULL as phone2,22185137 as phone3,NULL as phone4,93361637 as phone5),

telefonos_pivot as (
SELECT id,phone1 as phone from phones
UNION ALL
SELECT id,phone2 as phone from phones
UNION ALL
SELECT id,phone3 as phone from phones
UNION ALL
SELECT id,phone4 as phone from phones
UNION ALL
SELECT id,phone5 as phone from phones),

telefonos_pivot_clean as (
SELECT distinct * from telefonos_pivot
WHERE id IS NOT NULL AND phone IS NOT NULL)
SELECT id,to_json_string(array_agg(phone)) as phones FROM telefonos_pivot_clean
GROUP BY id

输出示例：

提取第一个手机号直接调用JSON_EXTRACT_SCALAR(phones, '$[0]')即可。

方案2：生成分隔符拼接字符串 #

如果项目要求普通文本格式，可使用字符串聚合函数直接拼接，默认用逗号分隔，也可自定义分隔符：

• 标准SQL（BigQuery/Spark等）：用string_agg(distinct cast(phone as string), ',')，可省略单独去重步骤，代码更简洁
• MySQL：用group_concat(distinct phone separator ',')

优化后的精简代码（省略单独去重CTE）：

WITH phones as (
SELECT '6446177' as id, NULL as phone1,NULL as phone2,11572946 as phone3,NULL as phone4,11572946 as phone5 
UNION ALL
SELECT '8523122' as id, NULL as phone1,NULL as phone2,29703165 as phone3,NULL as phone4,29703165 as phone5 
UNION ALL
SELECT '5606494' as id, NULL as phone1,NULL as phone2,51156520 as phone3,NULL as phone4,32247153 as phone5
UNION ALL
SELECT '6560607' as id, 85137 as phone1,NULL as phone2,22185137 as phone3,NULL as phone4,93361637 as phone5),

telefonos_pivot as (
SELECT id,phone1 as phone from phones
UNION ALL
SELECT id,phone2 as phone from phones
UNION ALL
SELECT id,phone3 as phone from phones
UNION ALL
SELECT id,phone4 as phone from phones
UNION ALL
SELECT id,phone5 as phone from phones)

SELECT id,string_agg(distinct cast(phone as string), ',') as phones 
FROM telefonos_pivot
WHERE id IS NOT NULL AND phone IS NOT NULL
GROUP BY id

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