如何在BigQuery WHERE子句中使用JSON查询数组筛选数据

要在现有SQL基础上，添加筛选条件确保webhookStatuses数组的所有元素都是"OK"，可以用以下两种方法实现：

方法一：通过UNNEST结合聚合函数判断 #

利用UNNEST展开JSON数组，再用LOGICAL_AND聚合函数检查所有元素是否满足条件：

SELECT
  JSON_EXTRACT_SCALAR(conversation_signals, '$.turnSignals.noUserInput') AS No_User_Input,
  JSON_QUERY_ARRAY(conversation_signals, '$.turnSignals.webhookStatuses') AS webhook_status_0
FROM `gcp-bq.dev_dataset.bq_export_data`
WHERE
  JSON_EXTRACT_SCALAR(conversation_signals, '$.turnSignals.noUserInput') = 'true'
  AND (
    SELECT LOGICAL_AND(status = 'OK') 
    FROM UNNEST(JSON_QUERY_ARRAY(conversation_signals, '$.turnSignals.webhookStatuses')) AS status
  )

逻辑说明：子查询中把webhookStatuses数组展开为单独的行，LOGICAL_AND(status = 'OK')会返回true当且仅当所有展开的元素都等于"OK"。

方法二：用JSON_PATH_EXISTS直接判断（更简洁） #

借助BigQuery的JSON_PATH_EXISTS函数，检查数组中是否存在非"OK"的元素，若不存在则说明所有元素都是"OK"：

SELECT
  JSON_EXTRACT_SCALAR(conversation_signals, '$.turnSignals.noUserInput') AS No_User_Input,
  JSON_QUERY_ARRAY(conversation_signals, '$.turnSignals.webhookStatuses') AS webhook_status_0
FROM `gcp-bq.dev_dataset.bq_export_data`
WHERE
  JSON_EXTRACT_SCALAR(conversation_signals, '$.turnSignals.noUserInput') = 'true'
  AND NOT JSON_PATH_EXISTS(conversation_signals, '$.turnSignals.webhookStatuses[?(@ != "OK")]')

逻辑说明：JSONPath表达式$.turnSignals.webhookStatuses[?(@ != "OK")]会匹配数组中所有不等于"OK"的元素，NOT JSON_PATH_EXISTS意味着不存在这样的元素，即数组全为"OK"。

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