How do I search an array of hashes based on multiple keys in Rails Postgres?

Question

I'd like to have an array of hases that I can input and search.

add_column :drinkers, :habits, :text, array: true, default: []

drinker.habits << { year: DateTime.now.year, month: DateTime.now.month, drinker_id: drinker_id, napped_at_8am: true }

How do I search for drinkers based on multiple drinker.habits key values?

Drinker.select {|drinker| drinker[habits][:year] === 2020 && drinker[habits][:drinker_id] === 1 }

That would be my guess, but I can't find anything anywhere about multiple key value searches.

This is a very self-inflicted problem. Don't store a hash in a text column. Much less in a text in a array column. A JSON column would be an improvement but using a separate table and a join table is really the solution here. — max
– max, Commented Apr 10, 2020 at 13:46
The very reason you don't want to do this is because the data will not be queryable or indexable in any decent way if you shove a bunch of serialized data into a stringy type column. What you're doing here is really just pulling everything out of the database and then de-serializing and filtering it in Ruby. That won't scale at all. — max
– max, Commented Apr 10, 2020 at 13:50

max · Accepted Answer · 2020-04-10 14:21:43Z

This is really just a classic example of shooting yourself in the foot with bad database modeling.

Postgres arrays are a good tool for really specialized tasks where you have an array of simple scalar values such as integers or strings. They are not a good idea if you want to store hashes. If you shove a Ruby hash inside of an array column you get an non-queryable mess.

The JSON/JSONB type can store more complex structures like an array of objects and is a valid choice if the data defies any attempt to confirm to a schema. While you can query an array of objects with the includes operator:

SELECT *
FROM "drinkers"
WHERE habits @> '[{"year": 2020}]'

You are really not doing yourself any favors here if the data is structured. Once you step up the complexity the queries will be hideous and you won't have any indices that will speed them up either.

Instead you want to just face the facts that relational databases are tabular and the way to model data in a relational database is - you guessed it - tables.

class Drinker < ApplicationRecord
  has_many :habits
end

class Habit < ApplicationRecord
  belongs_to :drinker
end

Drinker.joins(:habits)
       .where(habits: { year: 2020 })

TLDR; array, JSON/JSONB and hstore are decent tools for really specialized jobs. But should never be your first choice for anything.

Collectives™ on Stack Overflow

How do I search an array of hashes based on multiple keys in Rails Postgres?

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