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
   

Answer 2

This is just a temporary patch until I add an Invite model, but casting the invitee column to text I can search the columns. Like so

SELECT * FROM auctions
WHERE data::text LIKE "%#{string I'm searching for}%"

So, AR:

Auction.where('data::text LIKE ?', "%#{string I'm searching for}%")