AWS S3 Select: WHERE clause with json array

Question

I have the following json structure:

      {
        "guid": "60f1a5d3-f082-4432-a023-eec2e211f586",
        "notebook": "esse",
        "title": "Tempor cillum consequat sunt aliquip laborum deserunt.",
        "tags": [
          "magna",
          "laborum",
          "pariatur",
          "sint",
          "ex"
        ]
      }, ...

Is it possible to filter that json structure by a tag (or tags) with S3 select?

For example:

SELECT * FROM s3object s where s[*].tags IN ('foo')

doesn't work. I want the json object back, so

SELECT * FROM s3object[*].tags s

isn't the right statement, because it delivers only the tag list.

Big thx

user_1457398 · Answer

I tested your same JSON with the following and it successfully matched on this expression:

   SELECT * FROM s3object s WHERE 'magna' IN s.tags

AWS S3 Select: WHERE clause with json array

1 Answers