英文:
Filtering the JSON data stored in Postgres based on key-value pairs
问题
我有一个存储在我的Postgres数据库中的JSONB对象，列名为cart。这是两个用户的购物车数据：
{
"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
}
]
}
这些数据存储在名为cart的列下。我尝试使用Gin索引，但对我正在做的事情不太清楚。我应该如何查询数据，以便找到所有购物车中有裤子的用户列表在Postgres中？另外，我是新手，渴望学习，如果能通过Golang进行实现将会很有帮助。谢谢，Pushkar Singh
英文:
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.<br>
Thanks, Pushkar Singh
答案1
得分: 2
USING gin ((cart->'items') jsonb_path_ops);
SELECT * FROM order2 WHERE cart->'items' @> '[{"name":"Trousers"}]';```
参考资料：
https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type
https://stackoverflow.com/questions/18404055/index-for-finding-an-element-in-a-json-array/18405706#18405706
<details>
<summary>英文:</summary>
```CREATE INDEX order2_cart_gin_idx ON order2
USING gin ((cart->'items') jsonb_path_ops);
SELECT * FROM order2 WHERE cart->'items' @> '[{"name":"Trousers"}]';
Refrences :
https://stackoverflow.com/questions/22736742/query-for-array-elements-inside-json-type
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论