CODEWITHSUNDEEP
jsonpostgresql
galiolio
galiolio

Reputation: 275

Query jsonb array in POSTGRESQL 9.6

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

Upvotes: 1

Views: 167

Answers (1)

Sandip Mavani
Sandip Mavani

Reputation: 120

first create Index 

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

then 

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

Upvotes: 1

Related Questions