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.

Are you should you transcribed the successful case correctly? I'm getting a syntax error on flights: flights: .... Could you have meant flights: {flights: ... — Peter Alfvin
– Peter Alfvin, Commented Jun 30, 2013 at 6:46

Peter Alfvin · Accepted Answer · 2013-07-01 23:04:12Z

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.

The JSON data type will be more useful as it matures and more functions come online for working with it. You can't do much with it right now but there will be functions for looking inside the JSON (similar to the current hstore functions); it is also a natural fit for pl/v8 procedures (which Rails foolishly frowns upon). Even though Rails wants to treat the database like a dumb spreadsheet, don't discount the utility of having your database check the consistency and integrity of your data.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related