Storing nested Hashes in PostgreSQL with Rails 4 (and Hstore)
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?
Answer
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