5 ms·
A JSON field type for Django
- jpdlla 13y agoThis looks great, but how does it compare to already existing options like django-jsonfield or jsonfield?
- aychedee 13y agoI 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.
- 13y ago
- 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
- damon_c 13y agoThanks for this! I use some of the various json fields that exist already but have been feeling like it is about time to start using the built in Postgres JSON support instead of just TextFields.
- kanja 13y agoHopefully this will soon be added to django core - it's one of the goals as part of Marc Tamlyn's kickstarter to improve postgres support in django. https://www.kickstarter.com/projects/mjtamlyn/improved-postgresql-support-in-django https://www.kickstarter.com/projects/mjtamlyn/improved-postg...
- deleted 13y ago[deleted]
- dangayle 13y agoYou should also get this up on Github quick :)
- aychedee 13y agoIt's probably more appropriate to submit a pull to one of the existing projects TBH.
- deleted 13y ago[deleted]
- andybak 13y agoRemember to add it here when it's released: https://www.djangopackages.com/grids/g/json-fields/ https://www.djangopackages.com/grids/g/json-fields/
- jessedhillon 13y agoReally not trying to troll here, but I want to know why anyone uses a Python ORM here other than SQLAlchemy? It's been a while since I used Django's ORM but I recall the comparison between the two being very heavily in sqla's favor.
- aaron-lebo 13y agoIt has been sometime since I used either one really intensively (I still use Django's off and on in maintenance), but for a long period of time, despite the advantages for sqla, Django was still a lot more newbie friendly. Not much fiddling with engines, metadata, etc. Not to mention knowing the form stuff was going to just work, compared to third-party solutions (as good as they are) with the other.
- acdha 13y agoThe Django community is focused on web apps, not e.g. off-line reporting, so a large percentage of the things which the ORM doesn't help with simply aren't a priority for most users. For something like 90% of the queries most people write, Django's ORM is easier to work with and the tight integration with model forms, the admin, etc. is a big selling point for the typical project on a deadline. When you do need to do complex joins or use database-specific features, the .raw() / .extra() queryset methods handle enough to avoid it being a deal-breaker, particularly since the crazier your performance / feature requirements the more likely it is that you're going to need to ditch an ORM altogether. EDIT: in case it wasn't clear, I have a ton of respect for SQLAlchemy. Nothing above should be seen as saying SQLA isn't good, merely that the choice of ORM usually isn't the most important factor in a project's decision.
- grantcox 13y agoI'm fairly new to Python (~6 months) and started with SQLAlchemy, but recently switched our app to Peewee (http://peewee.readthedocs.org http://peewee.readthedocs.org) because of frustrations with the SQLAlchemy "session". The standard method of scoped_session(sessionmaker(engine)) will return a thread-local session - so all data manipulation effectively shares a global session. - Every change needs to be followed with a commit() or rollback(), or you'll end up with rubbish in the session that some later call will inadvertantly commit. - If you want to do a general "update where" call, make sure you use "synchronize_session=False" and then session.expire_all(), otherwise the session will be out of date. It felt like every time I had code working with data, I also had a non-trivial amount of session management. It wasn't abstracting the database access away, it was making everything very SQLAlchemy session specific. To be honest I'm somewhat second guessing my decision, because Peewee is far from the Python standard. But it's simple, understandable and clear, and doesn't force a strange "don't forget to manage the global state" mindset onto everything.
- tonylampada 13y agoHave you looked at https://pypi.python.org/pypi/django-jsonfield/ https://pypi.python.org/pypi/django-jsonfield/ before building your json field? If so, can you elaborate about how those implementations compare with each other?
- tobych 13y agoMy understanding is that the field, when working with a PostgreSQL database, is stored as a JSON field in PostgreSQL, rather than just a text field. I don't think django-jsonfield can do that.
- cpbotha 13y agoThere are a number of existing jsonfield implementations. This one, probably the best of the lot, DOES make use of the PostgreSQL JSON field: https://github.com/bradjasper/django-jsonfield/blob/master/jsonfield/fields.py#L136 https://github.com/bradjasper/django-jsonfield/blob/master/j... It looks like your app does more in terms of casting data that comes from the field. Is this the major improvement?
- misiti3780 13y agoYou should use hstore to store json in postgres also - no? https://github.com/djangonauts/django-hstore https://github.com/djangonauts/django-hstore I think the issue with that is it converts everything to string so you need to use json.loads().
- petepete 13y agoNot quite, hstore doesn't support nesting.
- geweke 13y agoIt's not Django, but I've built something quite similar for Rails, and have really loved using it. The combination of RDBMS stability and the flexibility of schemaless content is really powerful -- it is (IMHO) the best of both worlds. Would love to hear people's experience with this kind of setup (no matter what software you're using)...I think it's a very interesting, and useful, direction to go, especially for new projects where you may not want the overhead of multiple storage systems. https://github.com/ageweke/flex_columns https://github.com/ageweke/flex_columns
- Pitarou 13y agoWhat are the engineering trade-offs here? Metric were migrating from CouchDB, so using a JSON field is the obvious solution, but what about for new projects? Compare it to say, Django-CMS, which achieves pretty much the same thing by storing the tree data-structure directly in SQL. The `django-mptt` library handles the details of efficiently implementing a tree structure in SQL, and `south` simplifies database scheme migration. As a programmer, which system would you rather work with?
- aychedee 13y agoFor myself? I don't want to do a migration whenever we add a new widget to our promotion or email builder, I certainly don't want to think about doing a data migration on every existing document. In this particular case we do not care about the contents of this field. There is already a lot of related information stored in the same row.