Given data structured like:
CREATE TABLE articles (id SERIAL, data JSON);
INSERT INTO articles (id, data)
VALUES (1, '{"color":["red", "blue"]}');
I'm having some issues querying for articles based on keys and values in the 'data' column.
How would I select all articles with the value 'red' for key 'color' in the data column?
How would I select all articles with the values 'red' and 'blue' for the key 'color' ignoring order?
How would I select all articles where the values are empty for key 'color'?
Postgres 9.4 improves support for this type of querying, but we are stuck on 9.3 for the time being and are having trouble figuring out the syntax. An ideal answer wouldn't necessitate writing custom postgres functions, but that would work too.
Thanks.
EDIT: I've tried adapting this Querying inside Postgres JSON arrays but am having trouble adapting it to work with strings
