how remove an element from json string column by activerecord

Question

I have a table has a string column contains json data each record is like

{"id":1,"type":"car","name":"bmw"}
{"id":2,"type":"car","name":"Fiat"}
{"id":3,"type":"truck","name":"RAM"}

now I need to make a migration to remove name's element from json string by rails ActiveRecord::Migration[5.0] to becomes like this

{"id":1,"type":"car"}
{"id":2,"type":"car"}
{"id":3,"type":"truck"}

maybe [this similar question](https://stackoverflow.com/questions/23490965/postgresql-remove-attribute-from-json-column) has what you need — BigRon, Jul 26 '17 at 15:52
not actually. Seems no body uses migrator to update records . somebody told me make a script and run it by rake . I don't know if it's best practice of doing that or not — Mini, Jul 26 '17 at 16:02
I've done the same via a rake script. If you have a lot of records (50K +) it will take a long time and tie up one postgres connection and some memory, but the impact really is minor I believe. Use `find_each` to reduce the memory impact. And make sure you have an extra postgres connection to spare, otherwise you'll have server workers or background threads with no access to the DB when they need it. — BigRon, Jul 26 '17 at 16:11

score 7 · Answer 1 · answered Aug 05 '20 at 15:46

7

If the database is Postgresql and the column is jsonb, then assuming you want to operate in the car table column

<MODEL_NAME>.update_all("car = car - 'name'")

It can be done via Rake task or a data migration

answered Aug 05 '20 at 15:46

Jose Elera

938
9
16

This is by far the superior solution. Takes milliseconds to perform on a large data set – bubbaspaarx Oct 22 '20 at 10:06

score 3 · Accepted Answer · answered Jul 26 '17 at 16:31

3

I've done it by rake script

desc 'Remove name'
  task remove_name: :environment do
    Entry.all.find_each do |entry|
      puts "Removing #{entry.id}"
      entry.data.delete("name")
      entry.save
    end
  end

answered Jul 26 '17 at 16:31

Mini

1,138
9
24

how remove an element from json string column by activerecord

2 Answers2