CODEWITHSUNDEEP
sqlgoogle-bigquerybigdataretool
TayOnTech
TayOnTech

Reputation: 13

SQL WHERE clause doesn't work with arrays

I currently using a tool named retool to query data from my bigquery instance. The problem is quite a few of the field names use arrays and I'm having bit of an issue filter field names with arrays in the WHERE clauses.

Here's the SQL statement:

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,
    security_result[0].severity AS severity,
    security_result[0].description,
    principal.group.product_object_id AS account_id
FROM
    `bq.datalake.events`
WHERE
    metadata.product_name = "AWS GuardDuty"
    AND security_result[0].severity = "HIGH"
    AND UNIX_SECONDS(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds)) < UNIX_SECONDS(TIMESTAMP "{{endingdate.value}}") 
    AND UNIX_SECONDS(TIMESTAMP_SECONDS(metadata.event_timestamp.seconds)) > UNIX_SECONDS(TIMESTAMP "{{startingdate.value}}");

Here's the error message:

No matching signature for operator = for argument types: INT64, STRING. Supported signature: ANY = ANY at [16:7]

I'm trying to filter field names that contain arrays

Upvotes: 0

Views: 104

Answers (1)

Abdul Wasae
Abdul Wasae

Reputation: 3688

The error message you're seeing,

No matching signature for operator = for argument types: INT64, STRING. Supported signature: ANY = ANY at [16:7]

points to a type mismatch issue in your SQL query. This error typically occurs when you're trying to compare values of different data types using the = operator. In your case, it's likely that the comparison security_result[0].severity = "HIGH" is the source of the error, suggesting that security_result[0].severity is not returning a string as expected. You can use the BigQuery web console to verify the data type of the severity field.

Upvotes: 0

Related Questions