CODEWITHSUNDEEP
sqljsonpostgresqlpostgresql-9.3
Filip
Filip

Reputation: 2344

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

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?

Upvotes: 4

Views: 526

Answers (1)

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28521

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';

Upvotes: 8

Related Questions