Unable to save array in json field in postgresql (rails) cannot distinguish Array from json

Question

This is the error I get when running db: migrate

rake aborted! can't cast Array to json 

This is my table.

  class CreateTrips < ActiveRecord::Migration def change create_table :trips do |t| t.json :flights t.timestamps end end end 

This is in the seeds.rb file

 flights = [{ depart_time_hour: 600, arrive_time_hour: 700, passengers: [ { user_id: 1, request: true } ] }] trip = Trip.create( { name: 'Flight', flights: flights.to_json } ) 

For some reason I cannot do this. If I do it.

 trip = Trip.create( { name: 'Flight', flights: { flights: flights.to_json } } ) 

It works. I don't want this because now I need to access the json array with trip.flights.flights. Not what I want.

Answer 1

Executive Summary: This is a known issue and is addressed in this porting request , which is awaiting merge at the time of this writing.

Long version:

Well, I can understand why and how it fails / succeeds based on Array / Hash. Here's a Rails method that does type conversion (from quoting.rb), and it clearly doesn't support casting from Ruby Array to Postgres json , while it does support casting from Hash . In addition, I put some debugging code at the beginning of this method and found that it doesn’t matter if you use flights or flights.to_json as the value, since the latter is converted to the former for the purposes of this casting. I'm going to do even more digging because I had no problem injecting the flights.to_json value into the json column using psql.

  def type_cast(value, column, array_member = false) return super(value, column) unless column case value when Range return super(value, column) unless /range$/ =~ column.sql_type PostgreSQLColumn.range_to_string(value) when NilClass if column.array && array_member 'NULL' elsif column.array value else super(value, column) end when Array case column.sql_type when 'point' then PostgreSQLColumn.point_to_string(value) else return super(value, column) unless column.array PostgreSQLColumn.array_to_string(value, column, self) end when String return super(value, column) unless 'bytea' == column.sql_type { :value => value, :format => 1 } when Hash case column.sql_type when 'hstore' then PostgreSQLColumn.hstore_to_string(value) when 'json' then PostgreSQLColumn.json_to_string(value) else super(value, column) end when IPAddr return super(value, column) unless ['inet','cidr'].include? column.sql_type PostgreSQLColumn.cidr_to_string(value) else super(value, column) end end 

I went ahead and added the following line to the Array case:

  when 'json' then PostgreSQLColumn.json_to_string(value) 

and then modified PostgreSQLColumn.json_to_string (in cast.rb) to work with Array arguments, as well as with the Hash type, and I was able to pass your use case.

I did not check if there are any problems or pull requests for it at this moment

By the way, I suppose you know you can get around this by using the text field instead of the json field. The only thing json provides you is that I know if the check is at the database level. If you think this is important, I would be interested to know why, since I have text fields with json content in the web application I'm working on, and I would like to know about the benefits of converting them, if any.