Storing nested Hashes in PostgreSQL with Rails 4 (and Hstore)

Question

I have a Rails app that aggregates a lot of data from Google API's. I store the JSON responses in MongoDB currently (so my Rails app has both pg and mongo). However, today, I've came across PostgreSQL Hstore extension, and I've decided to give it a try.

Unfortunately, I've ran into a problem. JSON given by API's is multiple levels deep, so Ruby Hash after JSON.parse contains hashes, which contain new hashes. However, Hstore is string key/value store, and it only goes 1 level deep. So hashes within first hash just become strings.

The really nasty hack I found to do is to eval the hashes that were turned into strings:

eval("{ "foo" => "bar" }")

I do not like this. Any tips on what to do? Should I keep using MongoDB or is there any better way to store multi-level deep hashes in PG?

Toot

Answer 1

You should try using JSON extension for Postgresql. It will do exactly what you want: validate and store JSON. Originally JSON extension was added in 9.2. Postgres 9.3 added more features for JSON extension including new operators and functions. And postgres 9.4 will have advanced indexing support for JSON so you'll be futureproof with this setup.

Related links: http://www.postgresql.org/docs/9.3/static/functions-json.html http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3#JSON:_Additional_functionality

Dibucaine

Answer 2

Nested Hstore is a gem that supports nested hashes (and arrays and other types) in hstores, achieving something similar to a document store like MongoDB. It uses a hybrid of hstore and JSON serialization. It hasn't been tested on Rails 4, though.

Rey

Thanks. Too late, but the idea is good, would have served my purpose well. – Toot

Answer 3

As the documentation of Hstore states, keys and values in Hstore are simply text strings. Hstore cannot store multi-level json objects, nor is it meant for that purpose.

So to make it simple, you cannot replace MongoDb with PostgreSQL by simply using Hstore. Instead, you'll need to create tables for each kind of objects like in any other relational databases. If the schema of your objects are very dynamic, it'd be better to keep using MongoDB.

Kassi

Answer 4

As a followup to the question, it seems that Postgresql 9.4 has some awesome goodies to offer:

1. Hstore is now nested and supports arrays, which means moving from simple key-value model to rich document-based model.
2. Hstore access to specified field is fast (thanks to binary representation)
3. Hstore operators can use GiST and GIN indexes
4. Json users can use functional GIN index and get considerable speedup
5. Hstore's binary representation can be used by json

source

Shush

Answer 5

Use the json data type to store json, not hstore.

Richardo

Answer 6

If you're not interested in turning your multi-level json into objects that can be stored in postgres, you can stick everything in a text field in postgres and serialize/deserialize a json string if you're looking for a multi-level key/value storage outside of mongo.

As mentioned, Hstore doesn't do what you want.

Haematogenous

Yeah, I guess I could do that. But I'd really like to have fields in database, without need of schema. – Toot

Then mongodb is for you! – Haematogenous

Also, better than storing json, is to use Rails serialize model helper, it uses :text db field, but converts from Ruby Hash to text and vice versa automatically. – Toot

Answer 7

setting[:quizzes] # => "{:score=>true, :percent=>true, :weight=>true}"
JSON.parse setting[:quizzes].gsub(/:(\w+)/){"\"#{$1}\""}.gsub('=>', ':')
# => {"score"=>true, "percent"=>true, "weight"=>true}

Behan

Please explain this code by describing what you've done and how it answers the question. So that the asker (and future readers) do not need to ask for clarification. – Thissa