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?

Why not use JSON operators/functions? In any case you will need to show an example of what is being stored to get any useful help on converting. — Adrian Klaver
– Adrian Klaver, Commented Jan 25, 2021 at 16:05
@AdrianKlaver I have given an example in the desc. Please see if it helps — QuadSquad
– QuadSquad, Commented Jan 25, 2021 at 16:41
That helps. One more question, is the data type json or jsonb? — Adrian Klaver
– Adrian Klaver, Commented Jan 25, 2021 at 16:54

Adrian Klaver · Accepted Answer · 2021-01-25 22:34:07Z

1

One example assuming a json field:

\d json_test 
               Table "public.json_test"
  Column   |  Type   | Collation | Nullable | Default 
-----------+---------+-----------+----------+---------
 id        | integer |           |          | 
 fld_json  | json    |           |          | 
 fld_jsonb | jsonb   |           |          | 
 fruits    | json    |           |          | 

insert into json_test (id, fruits) values (1, '["Apple", "Orange", "Banana"] ');
insert into json_test (id, fruits) values (2, '["Pear", "Orange", "Banana"] ');
insert into json_test (id, fruits) values (3, '["Pear", "Apple", "Banana"] ');

WITH fruits AS 
(SELECT 
    id, json_array_elements_text(fruits) fruit 
 FROM json_test) 
SELECT 
   id 
FROM 
   fruits
WHERE 
    fruit = 'Orange';
 id 
----
  1
  2

UPDATE Method to convert JSON array into Postgres array:

SELECT 
    array_agg(fruit) 
FROM 
    (SELECT 
        id, json_array_elements_text(fruits)AS fruit 
    FROM 
    json_test) AS elements 
GROUP BY 
    id;

 array_agg       
-----------------------
 {Pear,Apple,Banana}
 {Pear,Orange,Banana}
 {Apple,Orange,Banana}

This assumes the JSON array has homogeneous elements as that is a requirement for Postgres arrays.

A simpler method of finding rows that have 'Orange' in the json field:

SELECT 
    id, fruits 
FROM 
    json_test 
WHERE 
    fruits::jsonb ? 'Orange';

 id |             fruits             
----+--------------------------------
  1 | ["Apple", "Orange", "Banana"] 
  2 | ["Pear", "Orange", "Banana"]

edited Jan 25, 2021 at 22:34

answered Jan 25, 2021 at 17:17

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

4 Comments

engineersmnky Over a year ago

While this explains how to query a JSON field it does not answer the question which is "Can anyone please help me to write a migration to change the existing column(type: Json) to array type?" Also given that this uses a CTE it is not very rails friendly either

Adrian Klaver Over a year ago

See UPDATE to answer.

QuadSquad Over a year ago

@AdrianKlaver Like in the updated answer. Is there a way we can check presence of array of values? (eg: SELECT id, fruits FROM json_test WHERE fruits::jsonb ? ['Orange', 'apple'];)

Adrian Klaver Over a year ago

See here JSON operators, ` SELECT id, fruits FROM json_test WHERE fruits::jsonb ?| array['Orange', 'apple'];`

max · Accepted Answer · 2021-01-25 19:06:54Z

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

answered Jan 25, 2021 at 19:06

max

103k15 gold badges113 silver badges177 bronze badges

1 Comment

max Over a year ago

I'm not 100% sure that 'fruits = json_array_elements(old_fruits)' will work and I can't test it right now since my postgres install is broken. But you get the general approach - and hopefully why its a dumb idea.

Collectives™ on Stack Overflow

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

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related