Postgres SELECT * FROM Table where array has element

Question

I am using Postgres 9.3.2 to make a database of contacts.

Example: If i have a row in my table that looks something like this.

{
    firstName : "First name"
    lastName : "Last name"
    emails : ["[email protected]", "[email protected]", "[email protected]]
}

PS: firstName, lastName and emails are columns in my db and the value associated is the value for that column for that specific row.

I want to be able to query the db so that if i query for the email "[email protected]" the result is nothing but if i query for "[email protected]" the result will be the above row entry.

I dont think the query

"Select * from contactTable where emails="[email protected]""

will work. instead i want to do something like

"Select * from contactTable where emails contains "[email protected]""

any ideas on how to do this?

Answer 1

"Select * from contactTable where emails contains "[email protected]""

I think you want:

"Select * from contactTable where thejsonfield -> emails

Example setup, after fixing up your totally broken json:

CREATE TABLE contacts AS SELECT '{
    "firstName" : "First name",
    "lastName" : "Last name",
    "emails" : ["[email protected]", "[email protected]", "[email protected]"]
}'::json AS myjsonfield;

The following will work in PostgreSQL 9.4, but unfortunately does not in 9.3 due to the oversight of the missing json_array_elements_text function:

select * 
from contacts, 
lateral json_array_elements_text(myjsonfield -> 'emails') email
where email = '[email protected]';

For 9.3, you have to use a clumsier method to scan the json array for matching values:

select * 
from contacts, 
lateral json_array_length(myjsonfield -> 'emails') numemails, 
lateral generate_series(0, numemails) n 
WHERE json_array_element_text(myjsonfield -> 'emails', n) = '[email protected]';

You can't use the simple IN or = ANY constructs because (at this point) PostgreSQL doesn't understand that you might have a json array, so it'll fail with:

regress=> SELECT * FROM contacts WHERE '[email protected]' = ANY (myjsonfield->'emails');
ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT * FROM contacts WHERE '[email protected]' = ANY (myjsonfi...
                                                     ^

as it expects a PostgreSQL array, not a json array, and there's no convenient builtin to turn a json array into a PostgreSQL array yet.

Answer 2

Postgres has support for parsing JSON. Here is documentation: http://www.postgresql.org/docs/9.3/static/functions-json.html. I can't give you more detailed answer since you didn't provide exact data and schema, but it's easy to find the right function in documentation.