AR Query for jsonb attribute column

Question

I'm using Postgres for my db, and I have a column in my Auction model as a jsonb column. I want to create a query that searches the Auction's json column to see whether a user's name OR email exists in any Auction instance's json column.

Right now I have @auctions = Auction.where('invitees @> ?', {current_user.name.downcase => current_user.email.downcase}.to_json), but that only brings back exact key => value matches I want to know whether the name OR the email exists.

Answer 1

1. You're using the @> operator. The description for it is this:

   “Does the left JSON value contain the right JSON path/value entries at the top level?”

   You probably want the ? operator if you want to return a row when the key (name in your case) matches.

2. There's not a good way to search for values only in a JSON column (see this answer for more details), but you could check if the key exists alone or the key and value match exists.

3. The same ActiveRecord methods and chaining apply as when using non-JSON columns, namely where and where(…).or(where(…)):

   class Auction
     def self.by_invitee(user)
       name = user.name.downcase
       json = { name => user.email } # note: you should be downcasing emails anyways
       where('invitee ? :name', name: name).or(
         where('invitee @> :json', json: json)
       )
     end
   end