Getting error when trying to create array of json-objects in Rails 4 with PostgreSQL

Question

I'm trying to add a array of json-objects into one of my models. Running the migration works fine, as long as I don't include a default value, but trying to store any value results in a crash. When trying to use a empty array as default value, the same crash occurs when running the migration.

My migration:

class AddJsonExampleToMyModel < ActiveRecord::Migration
  def change
    add_column :my_models, 
               :json_example
               :json,
               array: true,
               default: []
  end
end

The error looks like:

PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: ALTER TABLE "my_models" ADD COLUMN "json_example" json[] DEFAULT   '[]'/.../db/migrate/20151013125334_add_json_example_to_my_model.rb:3:in     `change'
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR: malformed array literal: "[]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.
: ALTER TABLE "my_models" ADD COLUMN "json_example" json[] DEFAULT '[]'
/.../db/migrate/20151013125334_add_json_example_to_my_model.rb:3:in`change'
PG::InvalidTextRepresentation: ERROR:  malformed array literal: "[]"
DETAIL:  "[" must introduce explicitly-specified array dimensions.

Am I trying to do something that can't be done, or am I doing it incorrectly?

My setup: Rails 4.1.9, (PostgreSQL) 9.4.4, Ruby 2.1.0p0

This post might answer your question : http://stackoverflow.com/a/20746242/3228012 — Caillou, Oct 13 '15 at 14:44

score 4 · Accepted Answer · answered Oct 13 '15 at 15:26

Rails is trying to serialize the whole value as a JSON array, when Postgres is expecting a native array containing JSON. I'd recommend avoiding the Postgres array and storing a single JSON object, which can of course be an array. Rails 4.1.9 is capable of serializing that correctly.

This works for me in Ruby 2.1.0p0, Rails 4.1.9, Postgres 9.4.4:

add_column :things, :data, :json, default: []

> t = Thing.new
 => #<Thing id: nil, data: []>
> t.data = [{a: 1}]
 => [{:a=>1}]
> t.save
   (0.2ms)  BEGIN
  SQL (0.7ms)  INSERT INTO "things" ("data") VALUES ($1) RETURNING "id"  [["data", "[{\"a\":1}]"]]
   (0.5ms)  COMMIT
 => true
> Thing.first
  Thing Load (1.0ms)  SELECT  "things".* FROM "things"   ORDER BY "things"."id" ASC LIMIT 1
 => #<Thing id: 1, data: [{"a"=>1}]>

Note that Rails also has trouble seeing changes to the array if you modify it in place. That's true of any value, but arrays are more frequently manipulated that way. For example:

> t = Thing.create
   (0.2ms)  BEGIN
  SQL (0.4ms)  INSERT INTO "things" DEFAULT VALUES RETURNING "id"
   (4.7ms)  COMMIT
 => #<Thing id: 2, data: []>
> t.data << 1
 => [1]
> t.save
   (0.2ms)  BEGIN
   (0.2ms)  COMMIT
 => true
> t.reload.data
  Thing Load (0.3ms)  SELECT  "things".* FROM "things"  WHERE "things"."id" = $1 LIMIT 1  [["id", 2]]
 => []

save had nothing to do because it's the same array object that was loaded, even though we added a value. The fix is to either make new array objects or use *_will_change! to tell Rails the value is dirty.

> t.data << 1
 => [1]
> t.data_will_change!
 => [1]
> t.save
   (0.2ms)  BEGIN
  SQL (0.4ms)  UPDATE "things" SET "data" = $1 WHERE "things"."id" = 2  [["data", "[1]"]]
   (0.4ms)  COMMIT
 => true
> t.reload.data
  Thing Load (0.4ms)  SELECT  "things".* FROM "things"  WHERE "things"."id" = $1 LIMIT 1  [["id", 2]]
 => [1]

That worked just as you said, thanks a bunch! Actually forgot that arrays are valid JSON... Thanks also for the heads up regarding the trouble regarding modifications and saves! — Jesper Lugner, Oct 14 '15 at 08:05

Getting error when trying to create array of json-objects in Rails 4 with PostgreSQL

1 Answers1