How to search a postgres JSONb column with wildcards in ruby activerecord

Question

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 [email protected] and I do User.by_email("[email protected]") 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?

Jesper · Accepted Answer · 2016-04-01 20:08:57Z

5

You can do this:

User.where("metadata->'session'->'Primary'->>'Email' LIKE ?", '%@smith.com')

answered Apr 1, 2016 at 20:08

Jesper

4,5552 gold badges25 silver badges34 bronze badges

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

1 Comment

Tim Abell Over a year ago

Thanks, that worked! Note that you might want ILIKE for case insensitive searching stackoverflow.com/questions/7005302/…

Collectives™ on Stack Overflow

How to search a postgres JSONb column with wildcards in ruby activerecord

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related