Rails Postgres-Jsonb列查询 [英] Rails Postgres - Jsonb Column Query
本文介绍了Rails Postgres-Jsonb列查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个名为 available_quantity的带有jsonb列的Bundle表格。将具有样本值
I have a Bundle tabel with jsonb column named 'available_quantity'. which will have sample values
[{面额: 100,数量: 20},{面额: 1000,数量: 19}]
[{ "denomination": "100", "quantity": "20"}, { "denomination": "1000", "quantity": "19"}]
现在,我要查询数量少于50的所有记录。
Now, I want to query all records with quantity less than 50.
我尝试了此查询,
Bundle.where((available_quantity->>'quantity'):: numeric< 50)
Bundle.where("(available_quantity->>'quantity')::numeric < 50")
但是此返回空关系。
我该怎么办?
Table "offer_service.bundles"
Column | Type | Collation |
Nullable | Default
-----------------------+-----------------------------+-----------+----
------+--------------------------------------------------------
id | bigint | | not
null | nextval('offer_service.bundles_id_seq'::regclass)
project_id | bigint | |
|
item_type | character varying | |
|
item_id | bigint | |
|
status | integer | | not
null | 0
created_at | timestamp without time zone | | not
null |
updated_at | timestamp without time zone | | not
null |
denomination_quantity | jsonb | |
|
deleted_at | timestamp without time zone | |
|
available_quantity | jsonb | |
|
示例数据:
id: 2586, project_id: 3, item_type: "GiftCard", item_id: 659, status:
"activated", created_at: "2020-05-18 09:38:54", updated_at: "2020-05-
28 13:25:29", denomination_quantity: {"100"=>200, "1000"=>200},
deleted_at: nil, available_quantity: [{"quantity"=>16,
"denomination"=>"100"}, {"quantity"=>20, "denomination"=>1000}]
推荐答案
希望我们在这里使用子查询。
Hope we have to use sub query here.
Bundle.select("*").from(Bundle.select("*, jsonb_array_elements(available_quantity) as aq")).where("(aq ->> 'quantity')::numeric <= 50").distinct
欢迎重构上述查询的建议!
Suggestions on refactoring the above query is welcomed!
这篇关于Rails Postgres-Jsonb列查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文