如何在BigQuery中查询指定指标的JSON数据？

问题背景 #

我在BigQuery中存储了如下格式的JSON数据：

[{ "name": "total_video_views", "values": [{ "value": 3720 }] }, { "name": "total_video_views_unique", "values": [{ "value": 3648 }] }]

该JSON包含更多不同指标的条目。目前我只能通过索引位置查询特定指标的数值（例如获取name为total_video_views_unique的数值）：

SELECT JSON_EXTRACT(<MY_JSON_STRING>, '$[1].name'), JSON_EXTRACT(<MY_JSON_STRING>, '$[1].values[0].value')

请问如何实现不依赖索引的精准查询？

解决方案 #

这里提供两种实用的方法，根据你的需求选择即可：

方法1：展开JSON数组后过滤

这种方法适合需要批量处理多个指标，或者要对展开后的条目做更多操作的场景：

WITH sample_data AS (
  -- 替换成你的实际表和字段
  SELECT '''[{ "name": "total_video_views", "values": [{ "value": 3720 }] }, { "name": "total_video_views_unique", "values": [{ "value": 3648 }] }]''' AS my_json
)
SELECT
  JSON_EXTRACT_SCALAR(item, '$.name') AS metric_name,
  CAST(JSON_EXTRACT_SCALAR(item, '$.values[0].value') AS INT64) AS metric_value -- 按需转换数据类型
FROM sample_data,
UNNEST(JSON_EXTRACT_ARRAY(my_json)) AS item
WHERE JSON_EXTRACT_SCALAR(item, '$.name') = 'total_video_views_unique'

原理：用UNNEST(JSON_EXTRACT_ARRAY(my_json))把JSON数组拆分成独立的对象条目，然后通过WHERE子句精准匹配name字段，完全摆脱对数组索引的依赖。

方法2：使用JSONPath的过滤语法直接提取

如果只需要快速获取单个指定指标的数值，这种方法更简洁：

WITH sample_data AS (
  SELECT '''[{ "name": "total_video_views", "values": [{ "value": 3720 }] }, { "name": "total_video_views_unique", "values": [{ "value": 3648 }] }]''' AS my_json
)
SELECT
  CAST(JSON_EXTRACT_SCALAR(my_json, '$[*]?(@.name == "total_video_views_unique").values[0].value') AS INT64) AS metric_value
FROM sample_data

原理：利用BigQuery支持的JSONPath过滤语法$[*]?(@.name == "xxx")，直接在JSON路径中筛选出name匹配的对象，然后提取对应的value值。

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