Fetch records where json contains a particular object

Question

I have a postgres 9.6 table which has a json field config. I want to fetch records from this table where the json has a particular key value pair.

My table is as follows

CREATE TABLE features(
    id integer NOT NULL,
    plan character,
    config json NOT NULL
)

In the json field, I am storing a json in the form

[
    { "name": "A", "state": "active"},
    { "name": "B", "state": "inactive"},
    { "name": "C", "state": "active"}
]

Now, I am querying the database to fetch all the records for which the json field contains the key-value pair { "name": "B", "state": "inactive"}.

My query is as follows

select * from features where config @> '[{ "name": "B", "state": "inactive"}]';

However, I get an error

ERROR:  operator does not exist: config @> unknown

Any idea where I am going wrong here. Pointers will be highly appreciated. TIA !!!

Answer 1

Operator @> is only available for jsonb data type:

CREATE TABLE features(
    id integer NOT NULL,
    plan character,
    config jsonb NOT NULL
);
CREATE 

insert into features values(1,'a',' [ { "name": "A", "state": "active"}, { "name": "B", "state": "inactive"}, { "name": "C", "state": "active"} ]');
INSERT 0 1

select * from features where  config @> '[{ "name": "B", "state": "inactive"}]';
 id | plan |                                                  config                                                  
----+------+----------------------------------------------------------------------------------------------------------
  1 | a    | [{"name": "A", "state": "active"}, {"name": "B", "state": "inactive"}, {"name": "C", "state": "active"}]
(1 row)

With json data type in the table, you can use:

select * from 
 (select json_array_elements(config)::jsonb  as item from features) as setofjsonb
where item = '{"name": "B", "state": "inactive"}'::jsonb;
                item                
------------------------------------
 {"name": "B", "state": "inactive"}
(1 row)