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](https://stackoverflow.com/questions/19568123/query-for-element-of-array-in-json-column) — Paul Coleman, Oct 30 '17 at 15:16

score 1 · Accepted Answer · answered Nov 01 '17 at 08:55

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 01 '17 at 08:55

Sandip Mavani

1 Answers1