Can't store array in json field in postgresql (rails) can't cast Array to json

Question

This is the error I'm getting when I run 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 my 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 can't do this. If I do this.

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

It works. I don't want this though because now I have to access the json array with trip.flights.flights. Not the behavior I'm wanting.

Answer 1

Exec Summary: This is a known issue and is addressed in this pull request, which is awaiting merge as of this writing.

Long version:

Well, I can see why and how it's failing/succeeding based on Array/Hash. Here's the Rails method that's doing the 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. Also, 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, as the latter gets converted to the former for purposes of this casting. I'm going to do some more digging because I had no problem inserting the value of flights.to_json into a 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 changed PostgreSQLColumn.json_to_string (in cast.rb) to operate on arguments of Array as well as Hash type and I was able to get your use case to pass.

I haven't checked to see if there are any issues or pull requests on this at this point

BTW, I assume you know you can work around this by using a text field instead of a json field. The only thing that json provides you that I know if is validation at the database level. If you think that's important, I'd be interested in knowing why, as I've got some text fields with json content in the web app I'm working on and I'd like to know the benefit of converting them, if any.