gemart
gemart

Reputation: 376

AR Query for jsonb attribute column

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.

Upvotes: 0

Views: 771

Answers (2)

gemart
gemart

Reputation: 376

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}%")

Upvotes: 0

coreyward
coreyward

Reputation: 80041

  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
    

Upvotes: 2

Related Questions