How to keep array structure when saving record - activerecord/postgres

Question

I created an array structure of hashes and i created a column in postgres to save that structure using the migration below

class AddKeyDirectionsToEvent < ActiveRecord::Migration[5.0]
  def change
    add_column :calendar_events, :key_directions, :text, array:true, default: []
  end
end

now, the structure of the array is the one below

{
    :in => [
        [0] {
               :duration => "5 mins",
               :distance => "0.4 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        },
        [1] {
               :duration => "12 mins",
               :distance => "5.3 km",
            :travel_mode => "TRANSIT",
            :travel_type => "SUBWAY"
        },
        [2] {
               :duration => "9 mins",
               :distance => "0.7 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        }
    ]
}
{
    :out => [
        [0] {
               :duration => "10 mins",
               :distance => "0.7 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        },
        [1] {
               :duration => "12 mins",
               :distance => "5.3 km",
            :travel_mode => "TRANSIT",
            :travel_type => "SUBWAY"
        },
        [2] {
               :duration => "6 mins",
               :distance => "0.4 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        }
    ]
}

but for some reason in database it is saved like this

["{:in=>[{:duration=>\"5 mins\", :distance=>\"0.4 km\", :travel_mode=>\"WALKING\", :travel_type=>nil}, {:duration=>\"12 mins\", :distance=>\"5.3 km\", :travel_mode=>\"TRANSIT\", :travel_type=>\"SUBWAY\"}, {:duration=>\"9 mins\", :distance=>\"0.7 km\", :travel_mode=>\"WALKING\", :travel_type=>nil}]}", "{:out=>[{:duration=>\"10 mins\", :distance=>\"0.7 km\", :travel_mode=>\"WALKING\", :travel_type=>nil}, {:duration=>\"12 mins\", :distance=>\"5.3 km\", :travel_mode=>\"TRANSIT\", :travel_type=>\"SUBWAY\"}, {:duration=>\"6 mins\", :distance=>\"0.4 km\", :travel_mode=>\"WALKING\", :travel_type=>nil}]}"]

any ideas why? I tried chanign the type of array from :text, to :varchar and got same result. The only solution i found is using eval command to convert string back to array which is not ideal.

dnsh · Accepted Answer · 2016-10-06 13:16:31Z

1

You need to set column type to json.Use below migration command -

rails g migration AddKeyDirectionsToCalendarEvent key_directions:json

convert your structure to ruby array like this:

data = [{
    :in => [
        {
               :duration => "5 mins",
               :distance => "0.4 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        },
        {
               :duration => "12 mins",
               :distance => "5.3 km",
            :travel_mode => "TRANSIT",
            :travel_type => "SUBWAY"
        },
        {
               :duration => "9 mins",
               :distance => "0.7 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        }
    ]
},
{
    :out => [
        {
               :duration => "10 mins",
               :distance => "0.7 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        },
        {
               :duration => "12 mins",
               :distance => "5.3 km",
            :travel_mode => "TRANSIT",
            :travel_type => "SUBWAY"
        },
        {
               :duration => "6 mins",
               :distance => "0.4 km",
            :travel_mode => "WALKING",
            :travel_type => nil
        }
    ]
}]

Then do,

event.key_directions=data
event.save

which will be saved in database like this,

[["updated_at", 2016-10-06 13:11:26 UTC], ["key_directions", "[{\"in\":[{\"duration\":\"5 mins\",\"distance\":\"0.4 km\",\"travel_mode\":\"WALKING\",\"travel_type\":null},{\"duration\":\"12 mins\",\"distance\":\"5.3 km\",\"travel_mode\":\"TRANSIT\",\"travel_type\":\"SUBWAY\"},{\"duration\":\"9 mins\",\"distance\":\"0.7 km\",\"travel_mode\":\"WALKING\",\"travel_type\":null}]},{\"out\":[{\"duration\":\"10 mins\",\"distance\":\"0.7 km\",\"travel_mode\":\"WALKING\",\"travel_type\":null},{\"duration\":\"12 mins\",\"distance\":\"5.3 km\",\"travel_mode\":\"TRANSIT\",\"travel_type\":\"SUBWAY\"},{\"duration\":\"6 mins\",\"distance\":\"0.4 km\",\"travel_mode\":\"WALKING\",\"travel_type\":null}]}]"], ["id", 4]]

You will get array when you will access it

    event.key_directions

 => [{"in"=>[{"duration"=>"5 mins", "distance"=>"0.4 km", "travel_mode"=>"WALKING", "travel_type"=>nil}, {"duration"=>"12 mins", "distance"=>"5.3 km", "travel_mode"=>"TRANSIT", "travel_type"=>"SUBWAY"}, {"duration"=>"9 mins", "distance"=>"0.7 km", "travel_mode"=>"WALKING", "travel_type"=>nil}]}, {"out"=>[{"duration"=>"10 mins", "distance"=>"0.7 km", "travel_mode"=>"WALKING", "travel_type"=>nil}, {"duration"=>"12 mins", "distance"=>"5.3 km", "travel_mode"=>"TRANSIT", "travel_type"=>"SUBWAY"}, {"duration"=>"6 mins", "distance"=>"0.4 km", "travel_mode"=>"WALKING", "travel_type"=>nil}]}]

answered Oct 6, 2016 at 13:16

dnsh

3,6342 gold badges26 silver badges47 bronze badges

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

10 Comments

Petros Kyriakou Over a year ago

just tried this and gave me this

PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json DETAIL: Expected ":", but found ",". CONTEXT: JSON data, line 1: ...:travel_mode=>\"WALKING\", :travel_type=>nil}]}",... : UPDATE "calendar_events" SET "key_directions" = $1, "updated_at" = $2 WHERE "calendar_events"."id" = $3

dnsh Over a year ago

If you have already some records in database for this column you have to delete them first.

dnsh Over a year ago

can you show a snippet from schema.rb which have create_table "events" do .. end

Petros Kyriakou Over a year ago

added just now, the column name is key_directions

Petros Kyriakou Over a year ago

i added also the SQL generated

|

Collectives™ on Stack Overflow

How to keep array structure when saving record - activerecord/postgres

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related