Rails - How to query multiple arrays of hashes in Active Records

Question

I am working on a Ruby on Rails project and I have several tasks with this structure in a Postgresql database :

id: 8,
 name: "Task 1",
 due_date: Mon, 25 Jul 2022,
 finished: true,
 reviewed: false,
 note: nil,
 tags: [{"id"=>6, "name"=>"tag_1"}, {"id"=>7, "name"=>"tag_2"}],
 assigned_to: [{"id"=>7, "username"=>"Julia"}, {"id"=>8, "username"=>"Joe"}],
 user_id: 5,
 group_id: 4,
 created_at: Sat, 06 Aug 2022 01:09:31.135203000 UTC +00:00,
 updated_at: Sat, 06 Aug 2022 01:09:31.135203000 UTC +00:00,
 pending: false>

And I want to query those tags depending on the the tag id and the assigned_to id.

The column types are:

 t.jsonb "tags", default: [], array: true
 t.jsonb "assigned_to", default: [], array: true

I checked this source: Query on Postgres JSON array field in Rails

but couldn't figure out how it works, this is what I get:

Task.where("tags::jsonb @> ?", [{"id": 6}, {"id": 7}].to_json)
  Task Load (3.8ms)  SELECT "tasks".* FROM "tasks" WHERE (tags::jsonb @> '[{"id":6},{"id":7}]')
  Task Load (0.9ms)  SELECT "tasks".* FROM "tasks" WHERE (tags::jsonb @> '[{"id":6},{"id":7}]') /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]
=> #<Task::ActiveRecord_Relation:0x4290>

Is there a way to query it? Thanks!

The contents of your tags and assigned_to columns strongly suggests that your database relations are suboptimal. You really should have a tags table and another table for users (usernames?), with the appropriate has_many, or has_and_belongs_to_many relations with the tasks table. Then not only will your query be much easier, but life will be better in the future! — Les Nightingill, Aug 06 '22 at 19:02

Sebastián Palma · Answer 1 · 2022-08-07T17:54:13.873

You could use JSONB_TO_RECORDSET to extract tasks.tags->'id' and the same way tasks.assigned_to->'id' and have a couple of extra columns you can use for the filtering:

Task
  .from('tasks, JSONB_TO_RECORDSET(tasks.tags) AS tags(id int), JSONB_TO_RECORDSET(tasks.tags) AS assigned_to(id int)')
  .where('tags.id = ? AND assigned_to.id = ?', 6, 7)
# SELECT "tasks".* FROM JSONB_TO_RECORDSET(tasks.tags) AS tags(id int), JSONB_TO_RECORDSET(tasks.tags) AS assigned_to(id int) WHERE (tags.id = 6 AND assigned_to.id = 7)

Notice the Active Record where won't work using hashes (like where(tags: { id: 6, ... }) as one might intuitively think), so the binding has to be done manually.

Notice this works by using tags and assiged_to as jsonb not jsonb[] as it's in your example. So you might have to remove the array: true argument in your migration.

Rails - How to query multiple arrays of hashes in Active Records

1 Answers1