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.

Answer 2

You could simply parse the JSON on access:

class Preference < ActiveRecord::Base
  def prefs
    self[:pref] = json_parse_values self[:pref]
  end

  private
  def json_parse_values(hash)
    hash.map do |key, val|
      begin
        [key, (val.is_a?(String) ? JSON.parse(val) : val)]
      rescue JSON::ParserError => e  
        [key, val]
      end 
    end.to_h
  end
end

The parsing method attempts to parse a value only if its a string. If it raises a ParserError, we just use the string.