如何编写BigQuery SQL过滤JSON类型的Info.error数组数据？
2026-8-17
问题描述
原始数据表
| Row | Name | Info |
|---|---|---|
| 1 | Tom | {"count":0, "error":[0,"failed",0]} |
| 2 | Joe | {"count":0, "error":["failed","failed","failed"]} |
| 3 | Joe | {"count":0, "error":[0,0,0]} |
| 4 | Tom | {"count":0, "error":["failed","failed",0]} |
过滤需求
- 保留
Info.error数组全为0的记录 - 排除
Info.error数组全为"failed"的记录 - 保留
Info.error数组至少包含一个0的记录
期望输出
| Row | Name | Info |
|---|---|---|
| 1 | Tom | {"count":0, "error":[0,"failed",0]} |
| 3 | Joe | {"count":0, "error":[0,0,0]} |
| 4 | Tom | {"count":0, "error":["failed","failed",0]} |
求对应的BigQuery SQL语句。
解决方案
以下是满足需求的BigQuery SQL语句：
SELECT * FROM your_table_name WHERE -- 排除所有元素都是"failed"的记录 NOT (SELECT COUNT(*) FROM UNNEST(Info.error) elem WHERE elem != 'failed') = 0 -- 确保数组中至少存在一个0（包含全0的情况） AND EXISTS (SELECT 1 FROM UNNEST(Info.error) elem WHERE elem = 0)
逻辑说明
- 排除全"failed"记录：通过
UNNEST展开数组后，统计非"failed"元素的数量。如果数量为0，说明数组全是"failed"，用NOT排除这类行。 - 保留含0的记录：用
EXISTS检查数组中是否有元素等于0，全0的数组自然满足这个条件，因此无需额外单独判断全0的情况，一条条件即可覆盖需求中的规则1和3。
内容的提问来源于stack exchange，提问作者Hikaru
相关产品推荐
相关产品推荐
