I haven't looked into the postgres json field in depth yet, but it seems like you may know the answer. Is there any way of ensuring the structure / integrity of the data stored in it? Or is it currently considered to be totally freeform?
"the json data type has the advantage of checking that each stored value is a valid JSON value" - http://www.postgresql.org/docs/9.3/static/datatype-json.html
There are also a bunch of functions available to operate on a JSON field. Full info here: http://www.postgresql.org/docs/9.3/static/functions-json.htm.... What this means is that you can do pretty fast searches on the value of a key in a JSON object.
Just found this post which shows how you can do it.
CREATE TABLE products (
data JSON,
CONSTRAINT validate_id CHECK ((data->>'id')::integer >= 1 AND (data->>'id') IS NOT NULL ),
CONSTRAINT validate_name CHECK (length(data->>'name') > 0 AND (data->>'name') IS NOT NULL ),
CONSTRAINT validate_description CHECK (length(data->>'description') > 0 AND (data->>'description') IS NOT NULL ),
CONSTRAINT validate_price CHECK ((data->>'price')::decimal >= 0.0 AND (data->>'price') IS NOT NULL),
CONSTRAINT validate_currency CHECK (data->>'currency' = 'dollars' AND (data->>'currency') IS NOT NULL),
CONSTRAINT validate_in_stock CHECK ((data->>'in_stock')::integer >= 0 AND (data->>'in_stock') IS NOT NULL )
}
At the moment I have all my data in mongo but over time it's fallen out of shape. There's a large chunk of it that just needs to be stores (json field) but it would be nice to constrain some of it.
This can be more dynamic short term.