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

Possible duplicate of Query for element of array in JSON column — Paul Coleman
– Paul Coleman, Commented Oct 30, 2017 at 15:16

Sandip Mavani · Accepted Answer · 2017-11-01 08:55:51Z

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"} ]';

answered Nov 1, 2017 at 8:55

Sandip Mavani

1202 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1