如何在BigQuery中遍历JSON数组？实现类似JsonPath的查询

你可以通过以下两种方式实现类似$.phoneNumbers[*].type的JsonPath查询效果：

方法一：直接提取目标数组（最接近JsonPath写法） #

使用JSON_QUERY_ARRAY函数，配合支持通配符的JSON路径，一次性提取所有目标字段组成的数组：

WITH tbl AS (
  SELECT JSON '''{
      "firstName": "John",
      "lastName": "doe",
      "age": 26,
      "address": {
          "streetAddress": "naist street",
          "city": "Nara",
          "postalCode": "630-0192"
      },
      "phoneNumbers": [
          {
              "type": "iPhone",
              "number": "0123-4567-8888"
          },
          {
              "type": "home",
              "number": "0123-4567-8910"
          }
      ]
  }''' AS j
)
SELECT JSON_QUERY_ARRAY(j, '$.phoneNumbers[*].type') AS phone_types
FROM tbl

输出结果：

["iPhone", "home"]

方法二：拆分数组后处理（适合需额外逻辑的场景） #

如果需要对数组元素做过滤、转换等额外操作，可以先通过JSON_EXTRACT_ARRAY拆分数组，UNNEST展开后提取字段，最后用ARRAY_AGG聚合为数组：

WITH tbl AS (
  SELECT JSON '''{
      "firstName": "John",
      "lastName": "doe",
      "age": 26,
      "address": {
          "streetAddress": "naist street",
          "city": "Nara",
          "postalCode": "630-0192"
      },
      "phoneNumbers": [
          {
              "type": "iPhone",
              "number": "0123-4567-8888"
          },
          {
              "type": "home",
              "number": "0123-4567-8910"
          }
      ]
  }''' AS j
)
SELECT ARRAY_AGG(JSON_VALUE(phone_num, '$.type')) AS phone_types
FROM tbl, UNNEST(JSON_EXTRACT_ARRAY(j, '$.phoneNumbers')) AS phone_num

输出结果与方法一一致。如果不需要聚合为数组，直接去掉ARRAY_AGG即可得到每行一个type值的结果：

WITH tbl AS (
  SELECT JSON '''{
      "firstName": "John",
      "lastName": "doe",
      "age": 26,
      "address": {
          "streetAddress": "naist street",
          "city": "Nara",
          "postalCode": "630-0192"
      },
      "phoneNumbers": [
          {
              "type": "iPhone",
              "number": "0123-4567-8888"
          },
          {
              "type": "home",
              "number": "0123-4567-8910"
          }
      ]
  }''' AS j
)
SELECT JSON_VALUE(phone_num, '$.type') AS phone_type
FROM tbl, UNNEST(JSON_EXTRACT_ARRAY(j, '$.phoneNumbers')) AS phone_num

输出结果：

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