Choose from ... - based on a JSON value

Question

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

In addition, I have an extra column using a JSON data type (available from Postgres 9.2) with arbitrary length and arbitrary fields in JSON:

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

Using the new Postgres 9.3 features I want to return all rows with occupation = football, for example.

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

Is there any way to do this?

Answer 1

If I understand the manual correctly, you can access the JSON fields with the operators -> and ->> . The request will look like this:

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