如何在BigQuery中解析JSON数组并按元素类型统计数量？

我是BigQuery SQL使用者，表中某列存有如下JSON payload：

{"bboxes":[[0.371046781539917,0.5638647079467773,0.36434206366539,0.970723032951355],[0.5918399691581726,0.7923348546028137,0.33475741744041443,0.9601762294769287],[0.4485582411289215,0.674128532409668,0.34045588970184326,0.9819985628128052],[0.6591917276382446,0.7791954278945923,0.3623618483543396,0.7641286849975586],[0.5235867500305176,0.6976093649864197,0.28036248683929443,0.5006953477859497],[0.6841945052146912,0.9479447603225708,0.34488487243652344,0.9769046306610107],[0,0,0,0],[0,0,0,0],[0,0,0,0],[0,0,0,0]],"confidences":[0.15634897,0.14026128,0.12496623,0.087290496,0.05375478,0.050469983,0,0,0,0],"displayNames":["employee","employee","employee","employee","closed_register","employee","employee","employee","employee","employee"],"ids":["4187703889396498432","4187703889396498432","4187703889396498432","4187703889396498432","8799389907823886336","4187703889396498432","4187703889396498432","4187703889396498432","4187703889396498432","4187703889396498432"]}

需要按类型统计displayNames的数量（比如employee、closed_register的计数）。我已用JSON_QUERY提取该数组：

SELECT
JSON_QUERY(annotation,"$.displayNames") as detects
FROM
tableA

但返回的是JSON列表，请问如何对列表元素进行计数统计？

要统计JSON数组中各元素的数量，你需要先将数组拆分为单独的行，再进行分组计数。在BigQuery中可以用JSON_EXTRACT_ARRAY配合UNNEST来实现：

基础统计语句 #

SELECT
  display_name,
  COUNT(*) as count
FROM
  tableA,
  UNNEST(JSON_EXTRACT_ARRAY(annotation, "$.displayNames")) as display_name
GROUP BY
  display_name
ORDER BY
  count DESC

关键步骤说明 #

• JSON_EXTRACT_ARRAY(annotation, "$.displayNames")：将annotation列里的displayNames JSON数组转换为BigQuery原生数组类型（区别于JSON_QUERY返回的字符串）。
• UNNEST(...)：把数组中的每个元素拆分成独立的行，让每个displayName值单独占据一行。
• GROUP BY display_name + COUNT(*)：按名称分组后统计每个类型的出现次数，最后按计数降序排列方便查看。

可选：过滤无效元素 #

如果数组里存在空值、空字符串等无效内容，可以添加过滤条件优化结果：

SELECT
  display_name,
  COUNT(*) as count
FROM
  tableA,
  UNNEST(JSON_EXTRACT_ARRAY(annotation, "$.displayNames")) as display_name
WHERE
  display_name IS NOT NULL
  AND display_name != ""
GROUP BY
  display_name
ORDER BY
  count DESC

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