1

Found an issue with jsonb column data

Rails 4.2 and ruby 2.10, we are able to fetch jsonb column named data but not in the latest Rails 6.0.4.1 and ruby-3.0.2, it returning empty hash despite having data in the column

Can any body help me please to understand what causing an issue here

rails c

Loading development environment (Rails 4.2.7.1)

2.2.10 :001 > pp TestTable.find(1234)

<TestTable:0x007faba8255188

 id:1234,

 name: nil,

 script_id:123,

 is_active: true,

 is_default: false,

 created_at: Wed, 25 Aug 2021 03:40:33 UTC +00:00,

 updated_at: Wed, 25 Aug 2021 03:40:33 UTC +00:00,

 contacts_list: "user1",

 se_results: {}​​​​​​​​​​,

 data:

  {​​​​​​​​​​"OS"=>"",
   "AUTHOR"=>"user1",
   "VERSION"=> "10.3"
  }​​​​​​​​​​,

 debug_owner: "",

 transition: {​​​​​​​​​​}​​​​​​​​​​,

 status: nil>

$ rvm list

=* ruby-2.2.10 [ x86_64 ]

ruby-2.3.0 [ x86_64 ]

ruby-3.0.0 [ x86_64 ]

ruby-3.0.2 [ x86_64 ]

=> - current

=* - current && default

* - default

$

$ rvm use ruby-3.0.2

Using /Users/user2/.rvm/gems/ruby-3.0.2

$ rails c

Resolving dependencies... Loading development environment (Rails 6.0.4.1)

3.0.2 :001 > pp TestTable.find(329470)

#<TestTable:0x00007fa273e8cc28

 id: 1234,

 name: nil,

 script_id:123,

 is_active: true,

 is_default: false,

 created_at: Wed, 25 Aug 2021 03:40:33 UTC +00:00,

 updated_at: Wed, 25 Aug 2021 03:40:33 UTC +00:00,

 contacts_list: "user1",

 se_results: nil,

 data: {​​​​​​​​​​}​​​​​​​​​​,

 debug_owner: "",

 transition: nil,

 status: nil>

=>

the migration file

Table is created initially without the jsonb data column

class CreateTestTable < ActiveRecord::Migration
  def change
    create_table :test_table do |t|
      t.string :name
      t.belongs_to :script, foreign_key: true
      t.boolean :is_active
      t.boolean :is_default
      t.string :contacts_list
      t.string :se_results
      t.string :debug_owner
      t.string :status
      t.timestamps null: false
    end
  end
end

Later, we added jsonb column using below migration

class ColumnDataJsonb < ActiveRecord::Migration
  def change
    add_column :test_table, :data , :jsonb
    change_column_default :test_table, :data , '{}'
  end
end

if I switch back to rails 4.2 and check, I could see and able to fetch the data

Model: test_table.rb

class TestTable < ActiveRecord::Base

  DATA_ATTRIBUTES = [ :author, :version ]

  store :data, accessors: DATA_ATTRIBUTES, coder: ActiveSupportJsonProxy
end

active_support_json_proxy.rb

class ActiveSupportJsonProxy

  def self.dump(obj)
    ActiveSupport::JSON.encode(obj) unless obj.nil?
  end

  def self.load(obj)
    if obj.class == String
      ActiveSupport::JSON.decode(obj) if obj.present?
    else
      obj
    end
  end
end
raj_acharya
  • 665
  • 5
  • 17
  • Can we see the full migration please? It looks like you've overwritten the data column with the default – Mark Sep 09 '21 at 16:21
  • @Mark updated the migration file details in the question, Please check, Thanks. When switch back to rails 4, I could able to fetch data – raj_acharya Sep 09 '21 at 16:31
  • 1
    If switching back to rails 4 returns the data, then I was wrong and that's good news :) – Mark Sep 09 '21 at 16:34
  • When you run `TestTable.where(data: '{}')`, does that return the record in question? – Mark Sep 09 '21 at 16:34
  • @Mark No, it is not returning above record TestTable.where(data: '{}').pluck(:id) SELECT "test_table"."id" FROM "test_table" WHERE "test_table"."data" = '"{}"' ORDER BY test_table.name => [] Tried with TestTable.where(data: {}) it is also not returning the record – raj_acharya Sep 09 '21 at 16:47
  • Have you looked at the record in the database without Rails in the way (i.e. with `psql`) and if so, what does the JSON look like? Anything in your model class that does anything with `data`? – mu is too short Sep 09 '21 at 17:17
  • On modern versions of rails you just need to use `add_column :test_table, :data , :jsonb, default: {}`. – max Sep 09 '21 at 17:20
  • How did you go about upgrading? It is never recommended to jump full versions e.g. 4.2 to 6.0. The recommended upgrade path is always in steps [See here](https://guides.rubyonrails.org/upgrading_ruby_on_rails.html#the-upgrade-process) at a minimum you should go 4.2 -> 5.0 -> 5.2 -> 6.0 and test along the way. – engineersmnky Sep 09 '21 at 17:23
  • @muistooshort we are using store in model level for the column, I have updated model details in the question – raj_acharya Sep 09 '21 at 17:26
  • 1
    Well thats a big problem. Do not use `store` or `serialize` with native JSON/JSONB/Array columns (or really ever for that matter). The database driver handles it for you and using store will give you garbage data as you're double encoding it. So instead of an object you get a JSON string in the database that looks like `"\{\"foo\":\"bar\ \}"`. See the note in the docs https://api.rubyonrails.org/classes/ActiveRecord/Store.html – max Sep 09 '21 at 17:30
  • If you already have a bunch of garbage data that has been serialized you need to use something like `WHERE jsonb_typeof(data) = 'string'` to find it and rectify it by turning the data into actual JSON. – max Sep 09 '21 at 17:41
  • @max Thanks, let me check into this. – raj_acharya Sep 09 '21 at 17:52
  • I would suspect that your application may have been created before native JSON types were available or that the programmer who wrote it was following outdated advice. Your code didn't actually work in the sense that you had queryable JSON data in Rails 4. – max Sep 09 '21 at 17:55
  • @max The application is 3 to 4+ years old, I am trying to upgrade this to latest rails and ruby version. if I try to avoid store and update the code handling of json is it will work or should I need to take care anything in the DB migration side without impacting existing db records – raj_acharya Sep 09 '21 at 18:10
  • 2
    I would create a separate column (lets call it new_data) as JSONB and then take care of converting all your data. How exactly you want to go about that depends on the size of the database. If its small you can just load the records in batches and update them with something like `update(new_data: old_data)`. If its huge you might want need to parse the existing data in the DB and update the new column. Make backups before you do anything though. Once you have completed the task and verified the results drop the old column and rename the new column. – max Sep 09 '21 at 18:19
  • @max Thanks for the suggestion, Let me look into this – raj_acharya Sep 09 '21 at 18:30
  • 2
    Yeah, that's what I suspected and why I was asking about the model and the database. Listen to @max, convert all the data to real `jsonb` if you can afford the data conversion time, bridge your way there and convert the data piecemeal if you converting it all in one go is too much downtime. – mu is too short Sep 09 '21 at 18:32

0 Answers0