jsonpostgresqlpostgresql-json

How to query array in Postgres json column?


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?


Solution

  • 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';
    
    1. 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);
    
    1. With @>
    select * from test where content_type @> array['c'];