diff options
Diffstat (limited to 'docs/topics')
| -rw-r--r-- | docs/topics/db/queries.txt | 230 |
1 files changed, 230 insertions, 0 deletions
diff --git a/docs/topics/db/queries.txt b/docs/topics/db/queries.txt index c0d0f2b3e3..c0e141ea8d 100644 --- a/docs/topics/db/queries.txt +++ b/docs/topics/db/queries.txt @@ -798,6 +798,236 @@ being evaluated and therefore populate the cache:: Simply printing the queryset will not populate the cache. This is because the call to ``__repr__()`` only returns a slice of the entire queryset. +.. _querying-jsonfield: + +Querying ``JSONField`` +====================== + +Lookups implementation is different in :class:`~django.db.models.JSONField`, +mainly due to the existence of key transformations. To demonstrate, we will use +the following example model:: + + from django.db import models + + class Dog(models.Model): + name = models.CharField(max_length=200) + data = models.JSONField(null=True) + + def __str__(self): + return self.name + +Storing and querying for ``None`` +--------------------------------- + +As with other fields, storing ``None`` as the field's value will store it as +SQL ``NULL``. While not recommended, it is possible to store JSON scalar +``null`` instead of SQL ``NULL`` by using :class:`Value('null') +<django.db.models.Value>`. + +Whichever of the values is stored, when retrieved from the database, the Python +representation of the JSON scalar ``null`` is the same as SQL ``NULL``, i.e. +``None``. Therefore, it can be hard to distinguish between them. + +This only applies to ``None`` as the top-level value of the field. If ``None`` +is inside a :py:class:`list` or :py:class:`dict`, it will always be interpreted +as JSON ``null``. + +When querying, ``None`` value will always be interpreted as JSON ``null``. To +query for SQL ``NULL``, use :lookup:`isnull`:: + + >>> Dog.objects.create(name='Max', data=None) # SQL NULL. + <Dog: Max> + >>> Dog.objects.create(name='Archie', data=Value('null')) # JSON null. + <Dog: Archie> + >>> Dog.objects.filter(data=None) + <QuerySet [<Dog: Archie>]> + >>> Dog.objects.filter(data=Value('null')) + <QuerySet [<Dog: Archie>]> + >>> Dog.objects.filter(data__isnull=True) + <QuerySet [<Dog: Max>]> + >>> Dog.objects.filter(data__isnull=False) + <QuerySet [<Dog: Archie>]> + +Unless you are sure you wish to work with SQL ``NULL`` values, consider setting +``null=False`` and providing a suitable default for empty values, such as +``default=dict``. + +.. note:: + + Storing JSON scalar ``null`` does not violate :attr:`null=False + <django.db.models.Field.null>`. + +.. fieldlookup:: jsonfield.key + +Key, index, and path transforms +------------------------------- + +To query based on a given dictionary key, use that key as the lookup name:: + + >>> Dog.objects.create(name='Rufus', data={ + ... 'breed': 'labrador', + ... 'owner': { + ... 'name': 'Bob', + ... 'other_pets': [{ + ... 'name': 'Fishy', + ... }], + ... }, + ... }) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': None}) + <Dog: Meg> + >>> Dog.objects.filter(data__breed='collie') + <QuerySet [<Dog: Meg>]> + +Multiple keys can be chained together to form a path lookup:: + + >>> Dog.objects.filter(data__owner__name='Bob') + <QuerySet [<Dog: Rufus>]> + +If the key is an integer, it will be interpreted as an index transform in an +array:: + + >>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy') + <QuerySet [<Dog: Rufus>]> + +If the key you wish to query by clashes with the name of another lookup, use +the :lookup:`contains <jsonfield.contains>` lookup instead. + +To query for missing keys, use the ``isnull`` lookup:: + + >>> Dog.objects.create(name='Shep', data={'breed': 'collie'}) + <Dog: Shep> + >>> Dog.objects.filter(data__owner__isnull=True) + <QuerySet [<Dog: Shep>]> + +.. note:: + + The lookup examples given above implicitly use the :lookup:`exact` lookup. + Key, index, and path transforms can also be chained with: + :lookup:`contains`, :lookup:`icontains`, :lookup:`endswith`, + :lookup:`iendswith`, :lookup:`iexact`, :lookup:`regex`, :lookup:`iregex`, + :lookup:`startswith`, :lookup:`istartswith`, :lookup:`lt`, :lookup:`lte`, + :lookup:`gt`, and :lookup:`gte` lookups. + +.. warning:: + + Since any string could be a key in a JSON object, any lookup other than + those listed below will be interpreted as a key lookup. No errors are + raised. Be extra careful for typing mistakes, and always check your queries + work as you intend. + +.. admonition:: MariaDB and Oracle users + + Using :meth:`~django.db.models.query.QuerySet.order_by` on key, index, or + path transforms will sort the objects using the string representation of + the values. This is because MariaDB and Oracle Database do not provide a + function that converts JSON values into their equivalent SQL values. + +.. admonition:: Oracle users + + On Oracle Database, using ``None`` as the lookup value in an + :meth:`~django.db.models.query.QuerySet.exclude` query will return objects + that do not have ``null`` as the value at the given path, including objects + that do not have the path. On other database backends, the query will + return objects that have the path and the value is not ``null``. + +.. admonition:: PostgreSQL users + + On PostgreSQL, if only one key or index is used, the SQL operator ``->`` is + used. If multiple operators are used then the ``#>`` operator is used. + +Containment and key operations +------------------------------ + +.. fieldlookup:: jsonfield.contains + +``contains`` +~~~~~~~~~~~~ + +The :lookup:`contains` lookup is overridden on ``JSONField``. The returned +objects are those where the given ``dict`` of key-value pairs are all +contained in the top-level of the field. For example:: + + >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'}) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'}) + <Dog: Meg> + >>> Dog.objects.create(name='Fred', data={}) + <Dog: Fred> + >>> Dog.objects.filter(data__contains={'owner': 'Bob'}) + <QuerySet [<Dog: Rufus>, <Dog: Meg>]> + >>> Dog.objects.filter(data__contains={'breed': 'collie'}) + <QuerySet [<Dog: Meg>]> + +.. fieldlookup:: jsonfield.contained_by + +``contained_by`` +~~~~~~~~~~~~~~~~ + +This is the inverse of the :lookup:`contains <jsonfield.contains>` lookup - the +objects returned will be those where the key-value pairs on the object are a +subset of those in the value passed. For example:: + + >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador', 'owner': 'Bob'}) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'}) + <Dog: Meg> + >>> Dog.objects.create(name='Fred', data={}) + <Dog: Fred> + >>> Dog.objects.filter(data__contained_by={'breed': 'collie', 'owner': 'Bob'}) + <QuerySet [<Dog: Meg>, <Dog: Fred>]> + >>> Dog.objects.filter(data__contained_by={'breed': 'collie'}) + <QuerySet [<Dog: Fred>]> + +.. admonition:: Oracle + + ``contained_by`` is not supported on Oracle. + +.. fieldlookup:: jsonfield.has_key + +``has_key`` +~~~~~~~~~~~ + +Returns objects where the given key is in the top-level of the data. For +example:: + + >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'}) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'}) + <Dog: Meg> + >>> Dog.objects.filter(data__has_key='owner') + <QuerySet [<Dog: Meg>]> + +.. fieldlookup:: jsonfield.has_any_keys + +``has_keys`` +~~~~~~~~~~~~ + +Returns objects where all of the given keys are in the top-level of the data. +For example:: + + >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'}) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'breed': 'collie', 'owner': 'Bob'}) + <Dog: Meg> + >>> Dog.objects.filter(data__has_keys=['breed', 'owner']) + <QuerySet [<Dog: Meg>]> + +.. fieldlookup:: jsonfield.has_keys + +``has_any_keys`` +~~~~~~~~~~~~~~~~ + +Returns objects where any of the given keys are in the top-level of the data. +For example:: + + >>> Dog.objects.create(name='Rufus', data={'breed': 'labrador'}) + <Dog: Rufus> + >>> Dog.objects.create(name='Meg', data={'owner': 'Bob'}) + <Dog: Meg> + >>> Dog.objects.filter(data__has_any_keys=['owner', 'breed']) + <QuerySet [<Dog: Rufus>, <Dog: Meg>]> + .. _complex-lookups-with-q: Complex lookups with ``Q`` objects |