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"}

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
– Mini, Commented Jul 26, 2017 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
– BigRon, Commented Jul 26, 2017 at 16:11

Jose Elera · Accepted Answer · 2020-08-05 15:46:49Z

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 5, 2020 at 15:46

Jose Elera

9589 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bubbaspaarx Over a year ago

This is by far the superior solution. Takes milliseconds to perform on a large data set

Mini · Accepted Answer · 2017-07-26 16:31:12Z

4

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, 2017 at 16:31

Mini

1,19010 silver badges25 bronze badges

Collectives™ on Stack Overflow

how remove an element from json string column by activerecord

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related