How to properly query Postgresql JSONB array of hashes on Ruby on Rails 6?

Question

This is my column:

[
  { id: 1, value: 1, complete: true }, 
  { id: 2, value: 1, complete: false },
  { id: 3, value: 1, complete: true } 
]

First, is there a "correct" way to work with a jsonb scheme? should I redesign to work with a single json instead of the array of hashes?

I have about 200 entries on the database, the column status has 200 of those itens.

How would I perform a query to get the count of true/false?
How can I query for ALL complete itens? I can query for the database rows in which the json has an item complete, but I can't query for all the itens, in all rows of the database that are complete.

Appreciate the help, thank you

Your schema seems to be fixed. So the best thing would be not to use JSON at all but simple relational means. Best in terms of working with the data in SQL at least. — sticky bit
– sticky bit, Commented Apr 18, 2021 at 1:52
@tadman if that's going to be the help, would you please point to me where I can select inside the array of hashes into this documentation? I've spent hours on that document before asking the question, another set of hours after. Maybe I'm just overly blind on this, but I can't find it. — Manny
– Manny, Commented Apr 18, 2021 at 12:55

Manny · Accepted Answer · 2021-04-18 13:12:46Z

1

Aha! I found it here:

https://levelup.gitconnected.com/how-to-query-a-json-array-of-objects-as-a-recordset-in-postgresql-a81acec9fbc5

Say your dataset is like this:

[{
  "productid": "3",
  "name": "Virtual Keyboard",
  "price": "150.00"
}, {
  "productid": "1",
  "name": "Dell 123 Laptop Computer",
  "price": "1300.00"
},
{
  "productid": "8",
  "name": "LG Ultrawide Monitor",
  "price": "190.00"
}]

The proper way to count it, is like this:

select items.name, count(*) as num from 
purchases,jsonb_to_recordset(purchases.items_purchased) as items(name text)
group by items.name
order by num Desc

Works like a charm and is extremely fast. To do it in Rails, you need to use Model.find_by_sql(....) and indicate your select therem. I'm sure there are probably better ways to do it.

answered Apr 18, 2021 at 13:12

Manny

437 bronze badges

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

Collectives™ on Stack Overflow

How to properly query Postgresql JSONB array of hashes on Ruby on Rails 6?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related