Filtering the JSON data stored in Postgres based on key-value pairs

Question

I have a JSON object stored as JSONB in my Postgres database under the column name cart. This is the cart data for 2 users

"items": [
 {
   "name": "T-shirt",
   "price": 250,
   "item_id": 111,
   "quantity": 1
 },
 {
   "name": "Trousers",
   "price": 600,
   "item_id": 222,
   "quantity": 1
 }
]
}

&&&&

{
"items": [
 {
   "name": "Jeans",
   "price": 250,
   "item_id": 333,
   "quantity": 1
 },
 {
   "name": "Trousers",
   "price": 600,
   "item_id": 444,
   "quantity": 1
 }
]
}

This data is stored under The column name cart. I tried using Gin indexes but wasn't clear about what i was doing. How should i be able to query The data such that i can find the list of all users that have trousers as an item in their cart in Postgres? Also, i am New to this implementation and keen to learn So Would be helpful If the implementations are made through Golang.
Thanks, Pushkar Singh

pushkar singh · Accepted Answer · 2022-05-18 11:43:09Z

3

USING gin ((cart->'items') jsonb_path_ops);

SELECT * FROM order2 WHERE cart->'items' @> '[{"name":"Trousers"}]';

Refrences :

Query for array elements inside JSON type

Index for finding an element in a JSON array

answered May 18, 2022 at 11:43

pushkar singh

852 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

Filtering the JSON data stored in Postgres based on key-value pairs

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related