Query jsonb array in POSTGRESQL 9.6

Question

This is my json :

[{"state":"terminated"}]

How can I query this array to get the state in a where clause ?

I'd like to query my table and get every rows with the colunm containing a state equals to "terminated"

My table :

id     |   info
 1     |  [{"state":"terminated"}]

What I've tried :

select * from "myTable"
where info->>'state' = 'terminated' 

But it doesn't return any rows.

I checked in table I have a row with a state ="terminated"

EDIT :

The "info" column can contains more object than now.

Example :

 [{"state":"terminated"},{"anotherKey","anotherValue"}]

Thanks

Answer 1

first create Index

CREATE INDEX docs_data_idx ON myTable USING GIN (info jsonb_path_ops);

then

select * from myTable WHERE  info @> '[ { "state":"terminated"} ]';