Active Record Update All JSON Field

Question

So I have a model Item that has a huge postgresql JSON field called properties. Most of the time this field does not need to be queried on or changed, however we store price in this field.

I'm currently writing a script that updates this price, but there's only a few unique prices and thousands of Items so in order to save time I have a list of Items for each unique price and I'm attempting to do an update all:

Item.where(id: items).update_all("properties->>'price' = #{unique_price}")

But this gives me:

syntax error at or near "->>"

Is there a way to use update all to update a field in a postgres JSON field?

Answer 1

You need to use jsonb_set() function, here is an example:

Item.where(id: items).
     update_all(
       "properties = jsonb_set(properties, '{price}', to_json(#{unique_price}::int)::jsonb)"
     )

This would preserve all values and update only one key.

Read documentation

Answer 2

You can also do this

Item.where(id: items).each do |item|
  properties = item.properties
  item.update(properties: properties.merge({
    price: unique_price
  }))
end

The keyword merge will override the value of the key provided with the new value ie unique_price

Merge documentation is here