Rails: query postgres jsonb using where error

0
votes

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?

2
ruby-on-railspostgresqlactiverecord
In what model did you use this code? Which table has authorization column? - mechnicov
in the performance model / table, which has the authorization column. I will add that to the question. - Darkisa
Would wrapping authorization in quotes fix the issue? - crtag
Did you try this: left_outer_joins(:artists).where(artists: { id: account } ).or(left_outer_joins(:artists).where("authorization ->> ? = 'owner'", account)) - mechnicov
@crtag you found it. I had to wrap authorization in quotes and it has to be double quotes. Single quote would still throw an error. If you want, post an answer: .where('"authorization" @> ?', { account => "owner" }.to_json) and I will give you the check. - Darkisa

2 Answers

1
votes

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

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

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