Querying for array containment against a JSON column in PostgreSQL 9.3

Question

I'm running PostgreSQL 9.3. I have a column named "model" of type "json" (not jsonb). The content of the "model" column resembles the following:

{ "section": { "colors": ["red", "blue", "green"] } }

Is it possible to query for rows where model -> section -> colors contains "blue"?

Answer 1

There are no json comparison operators in 9.3. You can call the function json_array_elements() and cast results of the function to text. Distinct on used in case the json arrays contain duplicated elements:

select distinct on (id) t.*
from my_table t,
json_array_elements(model->'section'->'colors')
where value::text = '"blue"';

SqlFiddle