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?
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 )
}
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.
