如何在BigQuery中为JSON类型数据添加链式条件查询

原始查询如下，目标是从JSON数组中筛选出territory="CA" AND sales_start_date="2023-05-05"的元素：

with tbl as (
  select json '''{
  "products": {
    "product": [
      {
        "territory": "CA",
        "sales_start_date": "2023-05-05"
      },
      {
        "territory": "US",
        "sales_start_date": "2023-06-05"
      }
    ]
  }
}''' as j)
select json_extract_array(j, '$.products.product') from tbl

尝试UNNEST时遇到报错：

Second argument to IN UNNEST of type ARRAY is not supported because array element type is not equality comparable at [16:10]

这是因为json_extract_array返回的是ARRAY<JSON>类型，JSON类型无法直接用于等值比较，以下是两种可行的解决方法：

方法一：使用带过滤条件的JSON路径直接提取 #

BigQuery支持在JSON路径中添加过滤逻辑，类似你提供的XPath写法，直接用JSON_QUERY_ARRAY配合过滤路径即可提取符合条件的元素：

with tbl as (
  select json '''{
  "products": {
    "product": [
      {
        "territory": "CA",
        "sales_start_date": "2023-05-05"
      },
      {
        "territory": "US",
        "sales_start_date": "2023-06-05"
      }
    ]
  }
}''' as j)
select json_query_array(j, '$.products.product[?(@.territory == "CA" && @.sales_start_date == "2023-05-05")]') as filtered_products
from tbl

方法二：UNNEST后提取字段过滤 #

先将JSON数组UNNEST，再逐个提取字段进行条件判断，避开JSON类型无法直接比较的问题：

with tbl as (
  select json '''{
  "products": {
    "product": [
      {
        "territory": "CA",
        "sales_start_date": "2023-05-05"
      },
      {
        "territory": "US",
        "sales_start_date": "2023-06-05"
      }
    ]
  }
}''' as j)
select 
  json_extract_scalar(p, '$.territory') as territory,
  json_extract_scalar(p, '$.sales_start_date') as sales_start_date
from tbl,
unnest(json_extract_array(j, '$.products.product')) p
where json_extract_scalar(p, '$.territory') = 'CA' 
  and json_extract_scalar(p, '$.sales_start_date') = '2023-05-05'

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