0

Trying to query data on json file using S3-Select 

{
"groups_id":
    {
        "307225":
            {
                "created_at": "2015-02-10T17:24:15-08:00",
                "updated_at": "2017-09-06T17:25:22-07:00",
                "name": "Company 1",
                "company": true,
                "contact_name": "User 1",
                "email": "",
                "phone_number": "",
                "address": "",
                "website": "",
                "notes": "Testing",
                "id": "307225"
            },
        "1058565":
            {
                "created_at": "2017-04-02T23:44:10-07:00",
                "updated_at": "2017-07-18T17:39:21-07:00",
                "name": "Company 3",
                "company": true,
                "contact_name": "User 1",
                "email": "",
                "phone_number": "",
                "address": "",
                "website": "",
                "notes": null,
                "id": "1058565"
            }
    }
}

Can someone help to get the desired output using s3 select, based on the condition as WHERE contact_name='User 1'.

{"id": "307225", "name": "Company 1"},
{"id": "1058565","name": "Company 3"}

below are the queries we had tried,

Select s.groups_id['307225'].id from s3object s
Select s.groups_id['1058565'].id from s3object s

in the above queries, we had hardcoded the group_id and we are able to fetch below

{
    "id": "1058565"
}

but in our case groupid is dynamic so i am not understanding. how to handle that?

Prasad
  • 1
  • 4
  • Can you show your effort? SO is not a place to ask others to code for you. – jellycsc May 12 '20 at 13:12
  • Thanks for reply @jellycsc. i tried all the possibilities from my side. i mean, tried different queries on AWS console by selecting json files and writing queries there but seems those things are not working properly. so instead confusing others i thought i should asked direct question. still have updated few examples – Prasad May 13 '20 at 06:10

0 Answers0