How to query array in Postgres json column?-CodePudding

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?

CodePudding user response：

Updated for change column type from text[] to json

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

Demo

convert to jsonb and use ? operator (Postgres document)

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

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

With any

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

With @>

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