2

I have a postgres table (Users) that has a JSONb column (metadata). At the moment I am searching for a User by email and it works just fine except that the email must be an exact match.

So if a user has email bob@smith.com and I do User.by_email("bob@smith.com") it works grand. But I would like User.by_email("@smith.com") to return all users with an email that matches.

In my user.rb model I have the following:

scope :by_email, ->(email) { where("metadata @> ?", { session: { Primary: { Email: email.to_s.downcase} } }.to_json) }

Which is what allows User.by_email(email) to work. How can I modify that to find all users that have an email matching the string passed in?

The second part to this is that I would like to be able to search by User's name as well.

This would be done via scope :by_name, ->(name) { where("metadata @> ?", { session: { Primary: { FullName: name.to_s} } }.to_json) }

How can I do these both at the same time? If this was just simple activerecord I know I could do: 

User.where("full_name LIKE ? OR email LIKE", "%#{search}%","%#{search}%")

How can I do this with a JSONb column?

Zack
  • 2,377
  • 4
  • 25
  • 51

1 Answers1

5

You can do this:

User.where("metadata->'session'->'Primary'->>'Email' LIKE ?", '%@smith.com')
Jesper
  • 4,535
  • 2
  • 22
  • 34
  • 1
    Thanks, that worked! Note that you might want `ILIKE` for case insensitive searching https://stackoverflow.com/questions/7005302/postgresql-how-to-make-case-insensitive-query/19475095#19475095 – Tim Abell Jan 08 '20 at 11:53