How to query on array of struct column in AWS Athena? (parquet format)

Question

I have a array of struct column in table reports. (array>) the columns data look like

labels
---------------------------------------------------------------------------
[{key=name, value=cat}, {key=owner, value=meow}, {key=team, value=animal}]`
[]
[{key=name, value=leopard}]

How can I query this where I wanted to find records which has key=name and value=cat, and key=owner and value=meow. (i mean the first example).

user_16446214 · Answer

Check the below link. This is cloudtrail logs, where we have struct columns https://docs.aws.amazon.com/athena/latest/ug/cloudtrail-logs.html

Refer to the userIdentity column Also check below link https://docs.aws.amazon.com/athena/latest/ug/rows-and-structs.html

You can directly reference nested fields with a parent_field.child_field notation. Try:

SELECT
  my_field,
  my_field.a,
  my_field.b,
  my_field.c.d,
  my_field.c.e
FROM 
  my_table

How to query on array of struct column in AWS Athena? (parquet format)

1 Answers