Rename a certain key in a hash

Question

I have a column car_details with 2000 entries, each of which is a hash of info that looks like this:

{"capacity"=>"0",
 "wheels"=>"6",
 "weight"=>"3000",
 "engine_type"=>"Diesel",
 "horsepower"=>"350",
 "fuel_capacity"=>"35",
 "fuel_consumption"=>"30"}

Some cars have more details, some have less. I want to rename the "fuel_consumption" key to "mpg" on every car that has that key.

Answer 1

Well, a previous answer will generate 2000 requests, but you can use the REPLACE function instead. Both MySQL and PostgreSQL have that, so it will be like:

Car.update_all("car_details = REPLACE(car_details, 'fuel_consumption', 'mpg')")

Take a look at the update_all method for the conditions.

See also PostgreSQL string functions and MySQL string functions.

Answer 2

Answer posted by @Ivan Shamatov works very well and is particular important to have good performances on huge databases.

I tried it with a PostgreSQL database, on a jsonb column. To let it works we have to pay same attention to data type casting.

For example on a User model like this:

User < ActiveRecord::Base {
                   :id => :integer,
           :created_at => :datetime,
           :updated_at => :datetime,
                :email => :string,
           :first_name => :string,
            :last_name => :string,
          :custom_data => :jsonb
}

My goal was to rename a key, inside custom_data jsonb field. For example custom_data hash content from:

{
                "foo" => "bar",
           "date" => "1980-07-10"
}

to:

{
                "new_foo" => "bar",
             "date" => "1980-07-10"
}

For all users records present into my db.

We can execute this query:

old_key = 'foo'
new_key = 'new_foo'    
User.update_all("custom_data = REPLACE(custom_data::text, '#{old_key}'::text, '#{new_key}'::text)::jsonb")

This will only replace the target key (old_key), inside our jsonb hash, without changing hash values or other hash keys.

Note ::text and ::jsonb type casting!