Retool查询BigQuery时数组字段WHERE子句过滤失败问题排查

错误原因分析 #

报错No matching signature for operator = for argument types: INT64, STRING明确说明：你在WHERE子句中用字符串"HIGH"和INT64类型字段做相等比较，类型不兼容。问题出在security_result[0].severity = "HIGH"这一行——security_result数组中的severity字段实际是整数类型（比如用数字1-5表示不同严重级别），而非字符串。

同时，直接通过[0]取数组第一个元素过滤的逻辑有缺陷：如果数组中有多个元素，只要第一个不符合条件，即便后面有符合的记录也会被过滤，无法覆盖所有场景。

解决方案步骤 #

1. 确认severity的实际取值 #

先执行以下查询，查看security_result.severity的所有可能值，确认"HIGH"对应的数字：

SELECT DISTINCT s.severity
FROM `bq.datalake.events`, UNNEST(security_result) s
WHERE metadata.product_name = "AWS GuardDuty"

2. 修改SQL的过滤逻辑 #

用EXISTS结合UNNEST检查数组中是否存在符合条件的元素，同时修正类型不匹配问题，还可以简化时间比较的冗余转换：

假设查询后发现"HIGH"对应的数字是3，修改后的SQL如下：

SELECT
    principal.ip AS ip,
    target.resource.product_object_id AS instance_id,
    metadata.product_event_type,
    principal.resource.attribute.labels[0].value AS image_description,
    target.asset.attribute.cloud.vpc.id AS vpc,
    -- 取第一个符合条件的severity值
    (SELECT s.severity FROM UNNEST(security_result) s WHERE s.severity = 3 LIMIT 1) AS severity,
    (SELECT s.description FROM UNNEST(security_result) s WHERE s.severity = 3 LIMIT 1) AS description,
    principal.group.product_object_id AS account_id
FROM
    `bq.datalake.events`
WHERE
    metadata.product_name = "AWS GuardDuty"
    -- 检查数组中是否存在严重级别为HIGH（对应数字3）的元素
    AND EXISTS (
        SELECT 1 FROM UNNEST(security_result) s
        WHERE s.severity = 3
    )
    -- 简化时间比较，直接用秒级时间戳对比
    AND metadata.event_timestamp.seconds < UNIX_SECONDS(TIMESTAMP "{{endingdate.value}}") 
    AND metadata.event_timestamp.seconds > UNIX_SECONDS(TIMESTAMP "{{startingdate.value}}");

3. 可选：返回所有符合条件的数组元素 #

如果希望返回数组中所有严重级别为HIGH的元素，而非只取第一个，可以用ARRAY构造新数组：

SELECT
    principal.ip AS ip,
    target.resource.product_object_id AS instance_id,
    metadata.product_event_type,
    principal.resource.attribute.labels[0].value AS image_description,
    target.asset.attribute.cloud.vpc.id AS vpc,
    -- 构造包含所有HIGH级别结果的数组
    ARRAY(
        SELECT STRUCT(s.severity, s.description) 
        FROM UNNEST(security_result) s 
        WHERE s.severity = 3
    ) AS high_severity_results,
    principal.group.product_object_id AS account_id
FROM
    `bq.datalake.events`
WHERE
    metadata.product_name = "AWS GuardDuty"
    AND EXISTS (
        SELECT 1 FROM UNNEST(security_result) s
        WHERE s.severity = 3
    )
    AND metadata.event_timestamp.seconds < UNIX_SECONDS(TIMESTAMP "{{endingdate.value}}") 
    AND metadata.event_timestamp.seconds > UNIX_SECONDS(TIMESTAMP "{{startingdate.value}}");

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