AWS Athena WAF Logs

Question

Since AWS updated the WAF logs format, my queries don't work anymore as described here:

Using Athena to get terminatingrule from rulegrouplist in AWS WAF logs

I get this error:

SYNTAX_ERROR: line 1:224: Unexpected parameters (row("rulegroupid" varchar,"terminatingrule" varchar,"nonterminatingmatchingrules" array(row("action" varchar,"ruleid" varchar))), varchar(17)) for function json_extract. Expected: json_extract(varchar(x), JsonPath) , json_extract(json, JsonPath)

Does anyone know how to fix this syntax error?

thanks

Answer 1

Your column is no longer a string, and the JSON functions expect a string. It looks like you've changed your schema to use a struct for a column. Struct properties can be accessed directly, e.g. rulegrouplist[1].rulegroupid.