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

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