Google BigQuery中如何根据指定ID可靠提取JSON对象的值？

方法1：使用JSONPath过滤（推荐，简洁高效） #

BigQuery支持标准JSONPath语法，可直接在JSON_EXTRACT_SCALAR中通过过滤条件定位目标对象，完全不依赖数组内的位置：

SELECT
  JSON_EXTRACT_SCALAR(
    JSON_,
    '$[?(@.id == "B")].value'
  ) AS b_value
FROM (
  SELECT JSON '[
    {"id":"A","value":"1"},
    {"id":"B","value":"2"},
    {"id":"C","value":"John"},
    {"id":"D","value":null},
    {"id":"E","value":"random"}
  ]' AS JSON_
)

说明：$[?(@.id == "B")]会匹配数组中所有id等于"B"的对象，.value提取其对应值；JSON_EXTRACT_SCALAR直接返回字符串类型结果（若目标值为null则返回null）。

方法2：UNNEST展开数组后过滤 #

如果需要处理更复杂逻辑（比如存在多个匹配项、需额外计算），可先将JSON数组展开为行，再过滤提取：

SELECT
  MAX(IF(obj.id = "B", obj.value, NULL)) AS b_value
FROM (
  SELECT JSON '[
    {"id":"A","value":"1"},
    {"id":"B","value":"2"},
    {"id":"C","value":"John"},
    {"id":"D","value":null},
    {"id":"E","value":"random"}
  ]' AS JSON_
),
UNNEST(JSON_EXTRACT_ARRAY(JSON_)) AS json_obj,
UNNEST([STRUCT(
  JSON_EXTRACT_SCALAR(json_obj, '$.id') AS id,
  JSON_EXTRACT_SCALAR(json_obj, '$.value') AS value
)]) AS obj

说明：先用JSON_EXTRACT_ARRAY将JSON数组转为BigQuery数组，再通过UNNEST展开为每行一个对象，最后用IF和聚合函数（如MAX）提取目标值。若数组中有多个id为"B"的对象，MAX会返回其中一个；若需保留所有匹配结果，可去掉聚合逻辑。

内容的提问来源于stack exchange，提问作者Sebastian ten Berge