Thanks for the info - I'm thinking more along the lines of constraining the data within the valid json.
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 )
}
http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-da...
Re:A JSON field type for Django
by richardwhiuk on Thursday March 13, 2014 @01:18PM
(#7393186)
Why would you ever want to do this? Surely at this point you are better with a table with id, name, description, price, currency, in_stock and an extra data field for anything else - given you require the other fields.
Maybe - I need to test the performance, but for my application breaking things out into a table is a little slow. In all probability the constraints will be slower, but it's something to try.
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.
Depends on how much the data is going to be altered. Changing columns is not a free operation in data stores. They require you to rewrite all the rows in many cases.
This can be more dynamic short term.
"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.