Psql querying json arrays -


the json store in postgres db has following structure. need fetch rows in domains of email 'gmail.com'

row1:

{    "student_id": "123",    "name": "foo",    "emails":[       {         "id": "777",         "domain": "gmail.com"       },       {         "id": "888",         "domain": "gmail.com"       }    ] }  

row2:

{    "student_ id": "345",    "name": "booo",    "emails":[       {         "id": "444",         "domain": "gmail.com"       },       {         "id": "555",         "domain": "yahoo.com"       }    ] }  

in case, result should student student_id 123 i.e. row 1. please help.

given table name student , field name, holding json data, data.

with   mail_domain (     select       (data->'student_id')::text student_id,       (json_array_elements(data->'emails')->'domain')::text domain           student),    selected_student (     select       student_id           mail_domain           domain = '"gmail.com"'      except      select       student_id           mail_domain           domain != '"gmail.com"')  select   *   student   (data->'student_id')::text in (     select       student_id           selected_student); 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -