BigQuery中提取JSON数组内所有手机号的问题求助

核心解决思路 #

先提取JSON数组，再将数组展开为单行对象，最后提取每个对象中的手机号字段。

完整SQL示例 #

假设你的表名为your_table，Contacts是存储完整JSON结构的字段（格式示例：{"phones": [{"phone": "11111111", "type": "CELLPHONE"}, {"phone": "222222222", "type":"CELLPHONE"}, {"phone": "99999999", "type": "CELLPHONE"}]}），执行以下SQL即可提取所有手机号：

SELECT
  -- 保留原表的其他业务字段，比如主键ID
  id,
  -- 提取单个手机号文本
  JSON_EXTRACT_SCALAR(phone_item, '$.phone') AS phone_number,
  -- 可选：同时提取对应的手机号类型
  JSON_EXTRACT_SCALAR(phone_item, '$.type') AS phone_type
FROM
  your_table,
  -- 将phones数组展开为单行JSON对象
  UNNEST(JSON_EXTRACT_ARRAY(Contacts, '$.phones')) AS phone_item

关键函数说明 #

• JSON_EXTRACT_ARRAY(Contacts, '$.phones')：从Contacts字段中提取phones对应的JSON数组，返回数组类型数据。
• UNNEST(...) AS phone_item：将数组展开，每个数组元素（单个手机号对象）成为独立的一行，phone_item代表该行的JSON对象。
• JSON_EXTRACT_SCALAR(phone_item, '$.phone')：从单个JSON对象中提取phone字段的纯字符串值，相比JSON_EXTRACT，它会自动去除字符串的引号，直接返回手机号文本。

常见错误排查 #

如果UNNEST时报错，优先检查两点：

• 确认Contacts字段的JSON格式完整且合法，比如外层是否包含{}，phones字段确实是数组类型。
• 确认SQL语法正确：BigQuery中UNNEST需要和原表用逗号分隔（等价于交叉连接），不要遗漏逗号。

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