CODEWITHSUNDEEP
ruby-on-railspostgresqlactiverecord
L. Green
L. Green

Reputation: 244

Active Record Update All JSON Field

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?

Upvotes: 12

Views: 8951

Answers (3)

KARASZI István
KARASZI István

Reputation: 31467

What I came up with based on @Philidor's suggestion is very similar but with dynamic bindings:

assignment = ["field = jsonb_set(field, '{ name_of_the_key }', ?)", value.to_json]
scope.update_all(scope.model.sanitize_sql_for_assignment(assignment))

Upvotes: 0

Marvin Kang'
Marvin Kang'

Reputation: 198

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

Upvotes: 0

Roman Kiselenko
Roman Kiselenko

Reputation: 44370

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

Upvotes: 34

Related Questions