Rails - Postgres - jsonb column query

Question

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

{ "100": 50, "1000":10 }

Now, I want to query all keys with values less than 50.

I tried this query,

Bundle.where('available_quantity @> ?', {'100': 5}.to_json)

But this one gives me all the Bundle with available_quantity containing {100: 5}.

How can I do that? Is that even possible?

take a look here stackoverflow.com/questions/44531689/…. I think your keys should have one name for this to work, but multiple values — Joel Blum
– Joel Blum, Commented May 22, 2020 at 7:13
@Joel_Blum, yes if I have a key, then its easy. But looking for a way to do it with the existing DB structure. — Suganya Selvarajan
– Suganya Selvarajan, Commented May 22, 2020 at 7:33
@SuganyaSelvarajan More of a postgres question than Rails, I don't see that it's possible though — Joel Blum
– Joel Blum, Commented May 22, 2020 at 12:41

Sebastián Palma · Accepted Answer · 2020-05-22 07:36:13Z

1

You can use the ->> operator:

Bundle.where("(available_quantity->>'100')::int < 50")

answered May 22, 2020 at 7:36

Sebastián Palma

33.6k6 gold badges45 silver badges65 bronze badges

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

6 Comments

Suganya Selvarajan Over a year ago

@sebastian_palma, this is wroking. But I want to get all rows with any key having value less than 50.

Suganya Selvarajan Over a year ago

is it even possible to give multiple keys over here? Bundle.where("(available_quantity->>'100, 50')::int < 50") ???

Sebastián Palma Over a year ago

I think you can but you'd have to add a AND or OR operator, available_quantity->>'100, 50' isn't valid unless you have the very same JSON key for that.

Suganya Selvarajan Over a year ago

okay, I have updated my json to be stored as an array. sample: available_quantity: [{"denomination"=>"1000", "quantity"=>19}, {"denomination"=>"5000", "quantity"=>20}] . Now my query would look like Bundle.where("(available_quantity->>'quantity')::int < 50") . But it returns empty relation.

Sebastián Palma Over a year ago

This is a new and different error. Feel free to create a new question @SuganyaSelvarajan.

|

Collectives™ on Stack Overflow

Rails - Postgres - jsonb column query

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related