Rails Postgres - Jsonb Column Query

Question

I have a Bundle tabel with jsonb column named 'available_quantity'. which will have sample values

[{ "denomination": "100", "quantity": "20"}, { "denomination": "1000", "quantity": "19"}]

Now, I want to query all records with quantity less than 50.

I tried this query,

Bundle.where("(available_quantity->>'quantity')::numeric < 50")

But this return empty Relation.

How can I do that?

SCHEMA

                                            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                       |           |          
|

Example Data:

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}]

Can you share the schema for that table and an example of its data? — Sebastián Palma
– Sebastián Palma, Commented May 29, 2020 at 4:23

Suganya Selvarajan · Accepted Answer · 2020-05-28 15:53:09Z

1

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!

answered May 28, 2020 at 15:53

Suganya Selvarajan

1,1121 gold badge14 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

Rails Postgres - Jsonb Column Query

SCHEMA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

SCHEMA

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related