postgresql - How to write a migration to convert JSON field to Postgres Array for querying in rails?

Question

There is an old table with column type as JSON but only arrays are stored in this column. Even though I am storing array, I am not able to query this field using the ANY keyword (which will work on array type columns in Postgres like in this post)

Eg: let's say ['Apple', 'Orange', 'Banana'] is stored as Json in the fruits column, I want to query like Market.where(":name = ANY(fruits)", name: "Orange") and get all the markets with Oranges available.

Can anyone please help me to write a migration to change the existing column(type: Json) to array type?

question from:https://stackoverflow.com/questions/65887259/how-to-write-a-migration-to-convert-json-field-to-postgres-array-for-querying-in

Answer 1

class AddArrayFruitsToMarkets < ActiveRecord::Migration[6.0]
  def up
    rename_column :markets, :fruits, :old_fruits
    add_column :markets, :fruits, :string, array: true
    Market.update_all('fruits = json_array_elements(old_fruits)')
  end
end

class RemoveJsonFruitsFromMarkets < ActiveRecord::Migration[6.0]
  def up
    remove_column :markets, :old_fruits
  end
end

But really if you're going to do something why not create tables instead as you're not really improving anything?

class Fruit < ApplicationRecord
  validates :name, presence: true
  has_many :market_fruits
  has_many :markets, through: :market_fruits
end

class MarketFruit < ApplicationRecord
  belongs_to :market
  belongs_to :fruit
end

class Market < ApplicationRecord
  has_many :market_fruits
  has_many :fruits, through: :market_fruits

  def self.with_fruit(name)
    joins(:fruits)
      .where(fruits: { name: name })
  end

  def self.with_fruits(*names)
    left_joins(:fruits)
      .group(:id)
      .where(fruits: { name: names })
      .having('COUNT(fruits.*) >= ?', names.length) 
  end
end