CODEWITHSUNDEEP
djangodjango-postgresqldjango-jsonfield
darkhorse
darkhorse

Reputation: 8722

What is the difference between annotations and regular lookups using Django's JSONField?

You can query Django's JSONField, either by direct lookup, or by using annotations. Now I realize if you annotate a field, you can all sorts of complex queries, but for the very basic query, which one is actually the preferred method?

Example: Lets say I have model like so

class Document(models.Model):
    data = JSONField()

And then I store an object using the following command:

>>> Document.objects.create(data={'name': 'Foo', 'age': 24})

Now, the query I want is the most basic: Find all documents where data__name is 'Foo'. I can do this 2 ways, one using annotation, and one without, like so:

>>> from django.db.models.expressions import RawSQL
>>> Document.objects.filter(data__name='Foo')
>>> Document.objects.annotate(name = RawSQL("(data->>'name')::text", [])).filter(name='Foo')

So what exactly is the difference? And if I can make basic queries, why do I need to annotate? Provided of course I am not going to make complex queries.

Upvotes: 1

Views: 187

Answers (2)

jozo
jozo

Reputation: 4742

They are interchangable so it's matter of taste. I think Document.objects.filter(data__name='Foo') is better because:

  • It's easier to read
  • In the future, MariaDB or MySql can support JSON fields and your code will be able to run on both PostgreSQL and MariaDB.
  • Don't use RawSQL as a general rule. You can create security holes in your app.

Upvotes: 1

Endre Both
Endre Both

Reputation: 5730

There is no reason whatsoever to use raw SQL for queries where you can use ORM syntax. For someone who is conversant in SQL but less experienced with Django's ORM, RawSQL might provide an easier path to a certain result than the ORM, which has its own learning curve.

There might be more complex queries where the ORM runs into problems or where it might not give you the exact SQL query that you need. It is in these cases that RawSQL comes in handy – although the ORM is getting more feature-complete with every iteration, with

  • Cast (since 1.10),
  • Window functions (since 2.0),
  • a constantly growing array of wrappers for database functions
  • the ability to define custom wrappers for database functions with Func expressions (since 1.8) etc.

Upvotes: 2

Related Questions