CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-5jsonb
radubogdan
radubogdan

Reputation: 2834

Accessing jsonb column and attributes from a Rails 5 Model

I'm having a strange issue when I try to create a scope for one on my models that is accessing something in jsonb. The query looks fine by me but it seems to not find any records even though I'm pretty sure they exist!

After reading postgresql docs the following code should work.

Can anyone spot the problem?

scope :financing_offer_eq, ->(field, value) do
  where(
    "cars.financing_offer->>:field = :value",
    field: field,
    value: value
  )
end

Output

Car.financing_offer_eq(:down_payment, '10000')
  Car Load (0.5ms)  SELECT "cars".* FROM "cars" WHERE (cars.financing_offer->>'down_payment' = '10000')
=> []

Here is a sample car

[132] pry(main)> Car.find(15).financing_offer
  Car Load (0.5ms)  SELECT  "cars".* FROM "cars" WHERE "cars"."id" = $1 LIMIT $2  [["id", 15], ["LIMIT", 1]]
=> {"amount_of_loan"=>"10000", "total_amount"=>"10000", "total_cost"=>"10000", "borrowing_rate"=>"1.2", "annual_percentage_rate"=>"1.2", "monthly_payment"=>"1.2", "down_payment"=>"10000"}

#<Car:0x007fea76ebf358
 id: 15,
 ...
 removed irrelevant attributes
 ...
 financing_offer: {"amount_of_loan"=>"10000", "total_amount"=>"10000", "total_cost"=>"10000", "borrowing_rate"=>"1.2", "annual_percentage_rate"=>"1.2", "monthly_payment"=>"1.2", "down_payment"=>"10000"}>

Also tried running queries in the console:

Car.where("financing_offer ->>'{down_payment}' = '10000'")
  Car Load (0.4ms)  SELECT "cars".* FROM "cars" WHERE (financing_offer ->>'{down_payment}' = '10000')
=> []

Car.where("financing_offer ->> 'down_payment' = :value", value: '10000')
  Car Load (0.4ms)  SELECT "cars".* FROM "cars" WHERE (financing_offer ->> 'down_payment' = '10000')
=> []

Car.where("financing_offer ->> 'down_payment'= '10000'")
  Car Load (0.5ms)  SELECT "cars".* FROM "cars" WHERE (financing_offer->>'down_payment'= '10000')
=> []

Car.where('financing_offer @> ?', {down_payment: '10000'}.to_json)
  Car Load (1.9ms)  SELECT "cars".* FROM "cars" WHERE (financing_offer @> '{"down_payment":"10000"}')
=> []

If it makes any difference I'm using Storext gem for AR mapping, Rails 5, postgresql 9.4.5. It's also not working if I don't use storext or I use another mapping gem jsonb_accessor

Upvotes: 1

Views: 1385

Answers (1)

Juanito Fatas
Juanito Fatas

Reputation: 9969

I created a repo verified your scope works.

https://github.com/JuanitoFatas/rails-5-jsonb/commit/fb60e12bda07504ad71efe5c78aa1918661d353c

Maybe try these:

  • Search jsonb issue on gems GitHub page that may affects this
  • restart rails console
  • recreate database (rails db:drop db:create db:migrate)
  • restart your computer

Upvotes: 1

Related Questions