CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
Darkisa
Darkisa

Reputation: 2037

Rails: query postgres jsonb using where error

I have a jsonb column in my postgres performances table called authorization where I store the uuid of a user as a key and their authorization level as the value e.g. 

{ 'sf4wfw4fw4fwf4f': 'owner', 'ujdtud5vd9': 'editor' }

I use the below Rails query in my Performance model to search for all records where the user is an owner:

class Performance < ApplicationRecord

      def self.performing_or_owned_by(account)
        left_outer_joins(:artists)
          .where(artists: { id: account } )
          .or(Performance.left_outer_joins(:artists)
            # this is where the error happens
            .where("authorization @> ?", { account => "owner" }.to_json)
          ).order('lower(duration) DESC')
           .uniq
      end

end

Where account is the account uuid of the user. However, when I run the query I get the following error:

    ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "@>")
    LINE 1: ..._id" WHERE ("artists"."id" = $1 OR (authorization @> '{"28b5...

The generated SQL is:

SELECT "performances".* FROM "performances" 
LEFT OUTER JOIN "artist_performances" ON "artist_performances"."performance_id" = "performances"."id" 
LEFT OUTER JOIN "artists" ON "artists"."id" = "artist_performances"."artist_id" WHERE ("artists"."id" = $1 OR (authorization @> '{"28b5fc7f-3a31-473e-93d4-b36f3b913269":"owner"}')) 
ORDER BY lower(duration) DESC

I tried several things but keep getting the same error. Where am I going wrong?

Upvotes: 0

Views: 276

Answers (2)

crtag
crtag

Reputation: 2337

The solution as per comment in the original question is to wrap the authorization in double-quotes. Eg:

.where('"authorization" @> ?', { account => "owner" }.to_json)

Upvotes: 1

mechnicov
mechnicov

Reputation: 15248

The ->> operator gets a JSON object field as text.

So it looks you need this query:

left_outer_joins(:artists).
  where("artists.id = ? OR authorization ->> ? = 'owner'", account, account).
  order('lower(duration) DESC').
  uniq

Upvotes: 0

Related Questions