1

I have dumped data into a parquet file. When I use

SELECT * FROM s3object s LIMIT 1

it gives me the following result.

 {
    "name": "John",
    "age": "45",
    "country": "USA",
    "experience": [{
            "company": {
                "name": "ABC",
                "years": "10",
                "position": "Manager"
            }
        },
        {
            "company": {
                "name": "BBC",
                "years": "2",
                "position": "Assistant"
            }
        }
    ]
}

I want to filter the result where company.name = "ABC" so, the output should be looks like following.

{
    "name": "John",
    "age": "45",
    "country": "USA",
    "experience": [{
            "company": {
                "name": "ABC",
                "years": "10",
                "position": "Manager"
            }
        }
    ]
}

or this

{
    "name": "John",
    "age": "45",
    "country": "USA",
    "experience.company.name": "ABC",
    "experience.company.years": "10",
    "experience.company.position": "Manager"
}

Any support is highly appreciated. Thanks.

Natasha Perera
  • 175
  • 12
  • have you tried Boto3? – Vivs May 16 '21 at 05:25
  • how about `SELECT * FROM s3object AS s WHERE s.experience.company.name = "ABC"` ? – blackraven May 16 '21 at 05:59
  • Thank you for your comments. I used the S3 GUI to run the sql query. If I can get the result hope to use it in boto3. I tried s.experience.company.name = ‘ABC’. But it did not show any result. – Natasha Perera May 17 '21 at 00:58
  • S3 Select supposedly supports PartiQL so it might be possible to write this using an EXISTS subquery, similar to the example in https://partiql.org/tutorial.html#use-case-checking-whether-a-nested-collection-satisfies-a-condition – Jörn Horstmann May 17 '21 at 13:44
  • Thanks. Can you tell me how should I apply PartiQL to the above query? – Natasha Perera May 17 '21 at 14:04

0 Answers0