Postgresql how to create new field in existing jsonb column

How to create new field in existing jsonb column using Rails migration and Active Record

Here is an example of how to add a new field called status_number with a value of new_value to a JSONB column called data in a table called services

UPDATE services
SET data = jsonb_set(data, '{status_number}', '"new value"')
WHERE id = 1;

To create a new field in an existing JSONB column in PostgreSQL use the jsonb_set function. The jsonb_set function allows you to modify a JSONB object by specifying a path to the field you want to modify with new value

In this example, the jsonb_set function takes three arguments:

• JSONB object to modify
• path to the new field
• new value

How to create new field in existing jsonb column with the value from another jsonb field for all the records in the table

Add new field status_number to a JSONB column called data in a table called services, where the value for status_number is taken from an existing field called account_number

UPDATE services
SET data = jsonb_set(data, '{status_number}', data->'account_number')
WHERE id = 1;

To apply this update to all the records in the table, simply remove the WHERE clause. The resulting query would look like this:

UPDATE services
SET data = jsonb_set(data, '{status_number}', data->'account_number')

How to remove field in existing jsonb column for all the records in the table

To remove a field from an existing JSONB column for all the records in a table use JSONB Operators from Postgresql Documentation

Remove a field called status_number from a JSONB column called data in a table called services

UPDATE services
SET data = data - 'status_number'
WHERE id = 1;

Use Rails to do database manipulations with JSONB columns

Create a Rails DB migration:

bin/rails g migration update_services_data_account_number

Write up and down methods for Rails database migration:

class UpdateServicesDataAccountNumber < ActiveRecord::Migration[6.1]
  def up
    sql = <<-SQL.squish
      UPDATE services
      SET data = jsonb_set(data, '{status_number}', data->'account_number')
      ;
    SQL
    execute(sql)
  end

  def down
    sql = <<-SQL.squish
      UPDATE services
      SET data = data - 'status_number'
      ;
    SQL
    execute(sql)
  end
end

Run Rails DB migration for development environment

bin/rails db:migrate

Run Rails DB migration for test environment

RAILS_ENV=test bin/rails db:migrate

Now new status_number field in JSONB column data is present with value from account_number

This is all for today.

Congratulations 🏆 on learning about Postgresql jsonb column and use of Rails migrations.