I have a JSON stored in a file in s3 at bucket/yyyy/MM/dd/hh. Content of the file:
{
"id": 1,
"name": "John Doe",
"orderDetails": {
"productId": "0afc45s3sde",
"name": "keyboard",
"uri": "KeyboardUri"
}
}
{
"id": 2,
"name": "Tom",
"orderDetails": {
"productId": "2afc65s8sde",
"name": "Mouse",
"uri": "MouseUri"
}
}
When I create a table in Athena using AWS Glue crawler, it creates a table with the following attributes:
id(int)
name(string)
orderDetail(struct<...>)
partition_0
partition_1
partition_2
partition_3
On executing select * from db.tablename; it shows all the records.
I want to know if Athena is capable of querying the content of the column. For example:
I want to fetch the uri present in orderDetails for both the users(Id = 1,2). So once the query executes, the output should be:
KeyboardUri
MouseUri
Is there a way to do this with Athena?