Rails: query postgres jsonb using where error

Question

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?

In what model did you use this code? Which table has authorization column? — mechnicov
– mechnicov, Commented Feb 23, 2020 at 21:34
in the performance model / table, which has the authorization column. I will add that to the question. — Darkisa
– Darkisa, Commented Feb 23, 2020 at 21:39
Did you try this: left_outer_joins(:artists).where(artists: { id: account } ).or(left_outer_joins(:artists).where("authorization ->> ? = 'owner'", account)) — mechnicov
– mechnicov, Commented Feb 23, 2020 at 21:54
@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
– Darkisa, Commented Feb 24, 2020 at 0:06

crtag · Accepted Answer · 2020-02-24 02:08:42Z

1

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

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

answered Feb 24, 2020 at 2:08

crtag

2,3772 gold badges15 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mechnicov · Accepted Answer · 2020-02-23 22:07:40Z

0

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

answered Feb 23, 2020 at 22:07

mechnicov

16.2k5 gold badges48 silver badges69 bronze badges

1 Comment

Darkisa Over a year ago

Even with the ->> operator I still get the same error. My best guess is with something with how I am using quotations

Collectives™ on Stack Overflow

Rails: query postgres jsonb using where error

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related