JSONB in where clause postgres

user_16784835 0
Viewed 4063

I Have a data like this - JSONB Data

So i need to find out all rows with id=203498. How can i write a query for this ? Any Help ?

jsonb postgresql
2 Answers

Use the JSON containment operator @>:

WHERE jsoncol @> '[ { "id" = 203498 }]';
user_64643080

It depends your data so please add to example data but I will give you example data.

If your data like this

 id |                                     cars_info                                      
----+------------------------------------------------------------------------------------
  1 | {"id": 1, "sold": true, "brand": "Toyota", "color": ["red", "black"], "price": 285000}
  2 | {"id": 2, "sold": false, "brand": "Honda", "color": ["blue", "pink"], "price": 25000}
  3 | {"id": 3, "sold": true, "brand": "Mitsubishi", "color": ["black", "gray"], "price": 604520}

Your query is like this. Maybe it can occurs some errors but your query will be seem like that.

SELECT * FROM cars WHERE cars_info -> 'id' = '1';
user_56385860
Related