如何统计BigQuery表JSON字段中每个Id对应的address_book地址条目数

实现方案 #

你可以直接使用BigQuery的JSON处理函数完成统计，具体SQL如下：

SELECT
  Id,
  -- 统计address_book下的键总数，无地址或JSON格式异常时返回0
  IFNULL(ARRAY_LENGTH(JSON_KEYS(PARSE_JSON(user_json), '$.address_book')), 0) AS address_count
FROM
  `你的表的完整路径（项目名.数据集名.表名）`

逻辑说明 #

• 首先用PARSE_JSON将字符串类型的user_json字段转换为BigQuery可识别的JSON结构
• 再用JSON_KEYS提取address_book节点下的所有键名（也就是所有uaddr_xxxx标识），返回值为数组格式
• 用ARRAY_LENGTH计算数组长度，即可得到对应Id的地址总数
• 外层加IFNULL处理异常场景：如果对应行没有address_book节点、JSON格式非法，会返回0避免统计结果为空

如果需要统计全表所有Id的地址总和，额外加一层聚合即可：

SELECT
  SUM(IFNULL(ARRAY_LENGTH(JSON_KEYS(PARSE_JSON(user_json), '$.address_book')), 0)) AS total_address_count
FROM
  `你的表的完整路径（项目名.数据集名.表名）`

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