5 ms·
JSONField has been an absolute godsend in combination with Django's ORM. I had been using it with Postgres and will likely keep our backend the same, but I cann
by grenoire 6y ago
JSONField has been an absolute godsend in combination with Django's ORM. I had been using it with Postgres and will likely keep our backend the same, but I cannot recommend it enough. You will have to write some validation and schema code on top if you want your data to have similar (but weaker) guarantees to the usual typed fields; the benefits from the flexibility you get are immeasurable though.
- nojs 6y agoCan you give some examples where a relational schema isn’t suitable?
- kumarvvr 6y agoData that tends to change after the application is deployed. More importantly, JSONFields can be used as a substitute for EAV pattern.
- fastball 6y agoI use the JSONP column type in Postgres for data that is structured but user-supplied.
- orf 6y agoStoring historical webhook notifications
- dempseye 6y agoThe bonus with this is that at the start you are probably only interested in a few of the fields in the webhook, but as your application develops, you may need to extract more. If you've stored the JSON of previous webhooks, you have an easy migration path.
- rsinger87 6y agoStoring arbitrarily complex Boolean queries (think Elasticsearch's query syntqx). While this could be done in SQL tables, there are no gains with referential intregity and while there would be some in consistency, I don't think they're worth it.
- zmmmmm 6y agoSparse data. For example, if there are 1000 possible attributes, only 5% are populated for any given row. If you have 1000 columns you are going to have a ridiculously wide and mostly empty table.
- fabian2k 6y agoIn Postgres, NULL values take one bit of storage as far as I understand. So a table has to be very, very wide before this becomes a benefit. Of course if you have attributes that you don't control but are e.g. user-specified, JSONB column are a good choice.
- woadwarrior01 6y agoThe traditional approach to efficiently modelling data like that with relational databases is to use the EAV[1] model, which works quite well in practice. [1]: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80...
- fleetfox 6y agoThere are scenarios when you can't avoid EAV and then JSON is great even when it's for denormalized data.
- skinnyarms 6y agoSpeaking strictly of schema... Relational schema are flexible and allow for an optimizer to figure out how best to fetch your data based on the data are querying and filtering for and stored statistics about your data set. Document oriented storage is great when you don't need the optimizer because you already know how the data is written and read. This means you can bundle it up into small, single fetch documents. No statistics or optimizer necessary. This is great if you understand your use cases really well, and they never change (good luck with that) or you have a large distributed data set that would be tough on an analyzer.
- mrweasel 6y agoProduct data on a webshop. If you have a wide selection of product categories you’ll have a huge number of attributes that aren’t relevant across your categories. There’s a number of ways to deal with the issue, e.g. a model per category or an EAV database pattern (this is what platforms like Magento does). None are really ideal, but storing the product attributes as JSON works pretty well. Even more so when your database supports querying the JSON blob.
- metafunctor 6y agoIn projects I've been involved with, storing JSON in the database has often turned out to be a mistake. Django models create a well-defined self-documenting structure for your schema, are easy to evolve using migrations, and there's a wealth of tooling built on top. IMHO, these far outweigh the perceived convenience of simply storing some stuff in a JSON field. If you find yourself implementing your own validation and schema code for JSON fields, I'd say it's a sign that you should probably stop and migrate the data to Django models instead. There are some cases when storing JSON is fine, of course, but in my experience they are few and far between.
- StavrosK 6y ago100% agreed, JSON fields are good when you want to dump a bunch of data you don't care about the shape of, but if you need a schema, pull the data into actual fields.
- formerly_proven 6y agoFor a database like postgres, or even for something like SQLite, this mostly becomes a distinction without a whole lot of difference, since the database can index and access JSON structures just like regular columns.
- OJFord 6y agoAccess, but not constrain or type-check. And it's just harder to use, I'm with it being generally a 'mistake' (in the 'you will regret this later' sense) - it's better than text, but I'm in the camp of as much structure as possible ('you will thank yourself later').
- formerly_proven 6y ago> Access, but not constrain or type-check. You can create CHECK constraints involving JSON operations in postgres, which is the same way you'd enforce any constraint.
- alanfranz 6y agoTypical use case: There are a number of required fields that your app won't work without. Those should be columns, probably non-nullable columns. Then there're a bunch of optional fields. Traditionally, a number of nullable cols would be created. But they're ultimately messy (need to null check every accesses) and unneeded since you can replace them with a single json item. Keys are always optional, so you always need to check for presence or absence, and modern dbs support json natively in many clauses.
- StavrosK 6y agoFields are messy because you have to check for nulls, yet JSON keys aren't messy because you have to check for nulls?
- alanfranz 6y agoAll json keys are optional, so you ALWAYS need to check for their presence or absence. Mess exists when some key is nullable and some isn't. Checking for nullability on a key that can never be null isn't extremely elegant.
- StavrosK 6y agoWhy is that a mess? I don't see the difference.
- kerkeslager 6y agoHow long have you been using this? My intuition says this is one of those tradeoffs that is fast and easy in the short run, but over time becomes technical debt. I haven't used these over the long term, though.
- robertlagrant 6y agoYep - SQLAlchemy's JSONB type is awesome as well. It's made life so much easier for relatively unstructured data!
- oliwarner 6y agoWe've been using it pretty heavily and the ability to do deep queries like Person.objects.filter(data__family__spouse__name__istartswith="oli") is great for one-offs but the performance stinks. As it well should —this isn't a complaint— just a reminder that keeping denormalised, first party data that can be well indexed is invaluable when you're talking about huge datasets. It'd also be nice to layer in JSON Schema directly into the modelling but there is a draft-7 compliant Python project that you can use for this in the model's clean() method.
- agumonkey 6y ago> data__family__spouse__name__istartswith="oli" that's some syntax right there :)
- DasIch 6y agoPostgres allows you to create an index on keys within a JSONB column[0]. It may be the case that Django doesn't offer support for this but I would expect you can probably get around that with raw SQL. [0]: https://www.postgresql.org/docs/current/datatype-json.html#JSON-INDEXING https://www.postgresql.org/docs/current/datatype-json.html#J...
- Townley 6y agoIn addition to raw SQL, postgres JSON fields can be indexed with GinIndex https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/indexes/#django.contrib.postgres.indexes.GinIndex https://docs.djangoproject.com/en/3.0/ref/contrib/postgres/i...
- sandGorgon 6y agoLooks like similar to Pydantic (included by default in Fastapi)