3

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?

MLavoie
  • 9,671
  • 41
  • 36
  • 56
Huseyin Sahin
  • 211
  • 4
  • 16
  • 2
    Try it without the square brackets at the start and end of the file and without commas between records. See file examples in: [(50) How I used "Amazon S3 Select" to selectively query CSV/JSON data stored in S3. | LinkedIn](https://www.linkedin.com/pulse/how-i-used-amazon-s3-select-query-selective-data-stored-sharma/) – John Rotenstein Jul 03 '19 at 08:36
  • I see, thanks @JohnRotenstein, maybe it will work, but this structure is the way that i need (which is the standard array of custom objects) – Huseyin Sahin Jul 03 '19 at 19:39
  • In that case, the file cannot be used with Amazon S3 Select. – John Rotenstein Jul 03 '19 at 21:43

1 Answers1

14

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' 
datacarl
  • 2,591
  • 25
  • 21