sqlamazon-web-servicesamazon-s3amazon-s3-select

Can't make a query on a JSON by using AWS S3 Select


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?


Solution

  • 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'