4 ms·
I looked at lots of alternatives. But I needed something that supported the actual underlying Postgresql json type. Psycopg2 converts those automatically into P
by aychedee 13y ago
I looked at lots of alternatives. But I needed something that supported the actual underlying Postgresql json type. Psycopg2 converts those automatically into Python types. So we needed a JSON type that would accept: Python dicts, lists, strings, and JSON encoded objects, lists, and strings. None of them support that because they are all just storing the data as text in the backend.
- aidos 13y agoI 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?
- aychedee 13y agoPostgresql validates the field. From the docs: "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 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.html 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.
- aidos 13y agoThanks 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-data-validation.html http://blog.endpoint.com/2013/06/postgresql-as-nosql-with-da...
- richardwhiuk 13y agoWhy 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.
- jlouis 13y agoDepends 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.
- aidos 13y agoMaybe - 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.
- aychedee 13y agoOh cool, that's very interesting! Thanks for sharing.
- jpdlla 13y agoThanks for the answer. Would it be possible to query against this field?
- aychedee 13y agoNot properly using Django's ORM. That's something I'll write when we actually start to need it. At the moment. It's more of a wholesale document store. Right now you would have to write a custom SQL query, using the Postgresql JSON functions, and the Model.objects.raw(...) interface of provided by Django.
- dangayle 13y agoThe second you make it easy to query this, I'm dumping mongodb as my go to quick hack json store. Having one datastore > multiple datastores
- Jasber 13y agoI run django-jsonfield which has partial support for native Posgresql JSON type and more will be added as it's incorporated into Django: https://github.com/bradjasper/django-jsonfield/issues/55 https://github.com/bradjasper/django-jsonfield/issues/55 Happy to accept pull requests for stuff like this if you're interested in contributing.
- andrewingram 13y agohttps://github.com/niwibe/djorm-ext-pgjson https://github.com/niwibe/djorm-ext-pgjson There are similar libraries for hstore, arrays etc, They tend to follow the djorm-ext-* pattern. https://github.com/niwibe/djorm-ext-core https://github.com/niwibe/djorm-ext-core https://github.com/niwibe/djorm-ext-pgarray https://github.com/niwibe/djorm-ext-pgarray https://github.com/niwibe/djorm-ext-pgbytea https://github.com/niwibe/djorm-ext-pgbytea https://github.com/niwibe/djorm-ext-expressions https://github.com/niwibe/djorm-ext-expressions