I am trying to query a JSON object by using aws s3-select
. My JSON array structure is like this:
[
{
"title": "s3",
"url": "https://...",
"time": "2019-07-02",
"summary": "by using s3 select..."
},
{
"title": "athena",
"url": "https://...",
"time": "2019-07-01",
"summary": "by using athena..."
},
{
"title": "mysql",
"url": "https://...",
"time": "2019-06-30",
"summary": "by using mysql..."
}
]
All of the objects inside the array have the same property. Now I want to execute a query to return all objects whose title is equal to, let's say, mysql
or athena
.
I've tried a lot of different scripts in aws console but none of them worked. It returns either an empty array/ object or gives an error. For example:
select * from s3object s where s[*].title = 'athena' //NOT WORKING.
select * from S3Object[*] s where s.title = 'athena' //NOT WORKING.
Is my JSON array structure wrong (since my objects don't have a key name)? How can I achieve this?
You should select the array at the root level.
So first [*]
corresponds to root. Next [*]
the top level array in root.
Try below query, it would work:
select * from S3Object[*][*] s where s.title = 'athena'