How to query array in Postgres json column?

Question

Thanks in advance. Assume, I have a table that contains the value in an array something like this.

CREATE TABLE example (
 id serial4 NOT NULL,
 name varchar(100) NOT NULL,
 content_type json NULL
 CONSTRAINT example_pkey PRIMARY KEY (id)
);

id |name |content_type
-----------------------
 1 | P | ['a','b','c']
 2 | Q | ['a',]
 3 | R | ['b','c']
 4 | S | ['a','c']

I want to find which row contains 'c' in content_type

I have tried but couldn't get,

select * from table where ARRAY['c'] && content_type;

Is there someone to help me to build the query?

• Mai Truong: you can find rows where the array has all values equal to c with: SELECT * FROM table WHERE value = ALL (column)
• sticky bit: Unclear. Is content_type a char array or a JSON? Edit the question and add the CREATE statement of the table to clarify. And also why isn't this modeled the relational way? That could have easily been done with another table linking the content types. Then it would be a simple join query...
• Rakibul Islam: @stickybit, thank you for the suggestion. I could have modeled the relational way but for some reasons, I have to follow this. content_type is a JSON column.
• Bergi: @RakibulIslam Do you really need a JSON column? Can it also hold objects or primitive values instead of arrays? Can the arrays contain anything else than characters? If not, you should change the column type to char[] or text[], to make it an actual array column (and have Pooya's answer work)
• Rakibul Islam: thank you again. I will try and give feedback here.

Answer 1

Updated for change column type from text[] to json

If your column type is JSON you can use two scenarios:

Demo

1. convert to jsonb and use ? operator (Postgres document)

select * from test where content_type::jsonb ? 'c';

2. Use json_array_elements_text

select distinct on(t.id) t.*
from 
 test t
 cross join json_array_elements_text(content_type) je
where
 je.value = 'c';

---

Old scenario

You can use any function to check values exist in an array or according to Postgres document use array operator @>

Demo

1. With any

select * from test where 'c' = any(content_type);

2. With @>

select * from test where content_type @> array['c'];

• Rakibul Islam: As the data type of content_type is JSON, it shows "ERROR: op ANY/ALL (array) requires array on right side"
• Pooya: I updated the post for the JSON scenario