How to use PostreSQL hstore with nested array in Rails?

Question

Migration:

class Preference < ActiveRecord::Migration

  def change
    enable_extension 'hstore'

    create_table :preferences do |t|
      t.hstore :prefs
    end
  end
end

Model:

class Preference < ActiveRecord::Base
  store_accessor :prefs
end

This seems to work if prefs is a hash such as { email:'yes' }, but does not work for a hash with an array inside: { email:[ 'monday', 'tuesday' ]}.

When pulling the hash out, the array is saved as JSON.

Is there a good way to use hstore with nested hashes and arrays?

I did try adding array:true to the migration, but this just seemed to allow the saving of an array instead of a hash.

How to use Rails PostgreSQL hstore with nested hashes and arrays?

Rails 4.2.4, PostgreSQL 9.3.5

Answer 1

PostgreSQL hstore is intended to store key value pairs of text strings. Take a look at the hstore docs.

I've used the JSON type to store arrays as a value inside a hash. There are two JSON data types: JSON and JSONB. JSONB supports indexing but is a little slower whereas JSON does not support indexing but is faster. You can read about them here: JSON Type.

I would do the following:

class Preference < ActiveRecord::Migration
  def change
    create_table :preferences do |t|
      t.json :prefs
    end
  end
end

On a side note setting array: true on the hstore column would imply that you want to store an array of hstores.