select from ... - based on a value in JSON format

Question

Let's say I have a database table with several common columns such as name, gender, age, ...

Besides I have an additional column using the JSON datatype (available from Postgres 9.2) with arbitrary length and arbitrary fields in JSON:

{"occupation":"football"}

{"occupation":"football", "hair-colour":"black"}

{"hair-style":"curly"}

Using new features of Postgres 9.3 I want to return all rows with occupation = football for instance.

Something like this pseudo: select * from table where json_field.occupation = football

Is there a way of doing this?

duplicate: http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype — nif, Jul 10 '13 at 12:59
I've already seen this. I intend to avoid custom functions mentioned in UPDATE I in question. And structure in answer seems different from mine. — Filip, Jul 10 '13 at 13:04
The poster of the answer in the duplicate question used the `json_to_elements` function to create multiple rows from the array. So the structure is identical to yours which contains one JSON object each row. And no custom function was used. The solution is exactly the same you accepted. — nif, Jul 10 '13 at 14:07

score 8 · Accepted Answer · answered Jul 10 '13 at 13:34

8

If I understood the manual correctly, you can access JSON fields with -> and ->> operators. The query would look like:

SELECT *
FROM your_table
WHERE json_field ->> 'occupation' = 'football';

answered Jul 10 '13 at 13:34

Ihor Romanchenko

1 Answers1