skip to content

With Django's JSONField, how do you filter on a nested key, and how do a missing key, JSON null and SQL NULL differ in 6.1?

level: middleimportance: should knowfreq 45%

answer

  1. double underscores walk the document
  2. integers index arrays
  3. isnull on a key means absent
  4. JSONNull() is new in 6.1

basics

~10 s

Chain keys with double underscores: filter(data__owner__name="Bob"), integers index arrays. data__key__isnull=True finds a missing key; data=JSONNull() (6.1) matches top-level JSON null, data__isnull=True matches SQL NULL.

solid answer

~40 s

Each `__segment` after a `JSONField` name is a **key transform**, an integer segment is an **array index**, so `data__owner__pets__0__name="Fishy"` walks the document; transforms chain with lookups like `icontains` or `gt`, and `KT("data__owner__name")` gives the text value for `annotate()` or `order_by()`. Three kinds of empty differ. `data__owner__isnull=True` matches rows where the **key is absent**. `data__owner=None` (or `JSONNull()`) matches a key **present with JSON null**. At the top level, `data__isnull=True` finds **SQL NULL**, and Django 6.1 adds `JSONNull()` to store and match top-level **JSON null**; using `filter(data=None)` for that is deprecated in 6.1 and will mean SQL NULL in Django 7.0. Both nulls read back as Python `None`. Watch typos: an unknown lookup name is silently taken as a key.

code

python · 12 lines
python
from django.db.models import JSONNull
from django.db.models.fields.json import KT

from kennel.models import Dog

Dog.objects.filter(data__owner__name="Bob")           # nested key
Dog.objects.filter(data__owner__pets__0__name="Fishy") # array index
Dog.objects.filter(data__owner__isnull=True)           # key absent
Dog.objects.filter(data__owner=JSONNull())             # key present, JSON null
Dog.objects.filter(data=JSONNull())                    # whole value is JSON null (6.1)
Dog.objects.filter(data__isnull=True)                  # column is SQL NULL
Dog.objects.annotate(owner=KT("data__owner__name")).order_by("owner")

go deeper

for a junior

Recall the double-underscore path syntax and that integer segments index arrays.

for a middle

Explain has_key, contains, KT(), and the three empty states: missing key, JSON null and SQL NULL, with the lookup for each.

for a senior

Show awareness of the 6.1 JSONNull deprecation, silent typo lookups, non-exhaustive filter/exclude and the default=dict design choice.

for a principal

Decide which data belongs in JSON versus real columns, given that JSON paths lack constraints, typed migrations and clear null semantics.

## Key, index and path transforms A `models.JSONField` stores a JSON document. After the field name, every double-underscore segment in a filter keyword is a **transform** into that document: - **Key transform** — `Dog.objects.filter(data__breed="collie")` reads the `breed` key. - **Path** — chaining keys: `filter(data__owner__name="Bob")`. - **Index transform** — an integer segment indexes an array: `filter(data__owner__other_pets__0__name="Fishy")`. - **Negative index** — cannot be written as a Python keyword, so use unpacking: `filter(**{"data__owner__other_pets__-1__name": "Fishy"})`. Supported on PostgreSQL, on SQLite since 6.0 and on Oracle 21c+ since 6.1; not on MySQL or MariaDB. The final segment is compared with `exact` by default, but key transforms chain with `iexact`, `icontains`, `startswith`, `endswith`, `regex`, `lt`, `lte`, `gt`, `gte` and the containment/key lookups. ## Lookups that belong to JSONField itself | Lookup | Meaning | |---|---| | `data__has_key="owner"` | the top-level key exists | | `data__has_keys=["a", "b"]` | all listed keys exist | | `data__has_any_keys=["a", "b"]` | at least one exists | | `data__contains={"breed": "collie"}` | the pairs are contained at the top level (not on Oracle or SQLite) | | `data__contained_by={...}` | the inverse of `contains` | **Typos do not raise.** Because any string can be a JSON key, an unknown lookup name is interpreted as a key lookup. `data__owner__icontain="bo"` quietly looks for a key called `icontain`. If a real key clashes with a lookup name, use `contains` instead. ## Text values: `KT()` `from django.db.models.fields.json import KT` gives the **text value** of a path, for use in `annotate()`, `order_by()` or comparisons: ```python Dog.objects.annotate(owner_name=KT("data__owner__name")).order_by("owner_name") ``` Without it, a key transform yields a JSON value, which some backends compare and sort differently from text. ## Three kinds of "empty" This is where interviews dig. Given rows with `{"owner": "Bob"}`, `{"owner": null}`, `{}` and a column that is SQL NULL: 1. **Missing key** — `filter(data__owner__isnull=True)` matches rows that **lack** the key (and rows whose whole column is SQL NULL). `isnull=False` is the same as `has_key`. 2. **Key present with JSON null** — `filter(data__owner=None)` or `filter(data__owner=JSONNull())` matches `{"owner": null}` only. 3. **Top-level SQL NULL vs JSON null** — saving `data=None` stores **SQL NULL**; saving `data=JSONNull()` (new in **6.1**; earlier code used `Value(None, JSONField())`) stores the JSON scalar `null`. Match them with `data__isnull=True` and `data=JSONNull()` respectively. **Both nulls read back as Python `None`**, so you cannot tell them apart after loading. The docs therefore suggest `null=False` with `default=dict` unless you really need SQL NULL. Storing JSON `null` does not violate `null=False`. ## The 6.1 deprecation Before 6.1, `filter(data=None)` on a `JSONField` matched **JSON null**, unlike every other field. In 6.1 that usage emits `RemovedInDjango70Warning` telling you to use `JSONNull()`, or `__isnull` if you meant SQL NULL. After the deprecation period, `None` at the top level will compile to `IS NULL`. Key and index lookups such as `data__owner=None` are **unaffected**. ## Designing with JSONField Querying JSON well starts with deciding what goes into it: - **Keep queried, constrained data in real columns.** Values you filter on constantly, join on or need uniqueness for are better as fields with indexes and database constraints. - **Pick one representation of "empty".** With `null=False, default=dict`, a missing value is `{}` and SQL NULL never appears, so the three-way distinction above collapses to "key present or not". - **Document key names.** Nothing validates JSON keys, so a renamed key in writer code silently stops matching reader filters. A model `clean()` or a schema check at the boundary catches it earlier. - **Mind the backend.** `contains` and `contained_by` are unavailable on Oracle and SQLite, ordering by a key sorts by string representation on MariaDB and Oracle, and SQLite reads the strings `"true"`, `"false"` and `"null"` as the JSON values. ## Filter and exclude are not complements Because of how path queries compile, `filter(data__owner__name="Bob")` and `exclude(data__owner__name="Bob")` together need not cover every row: rows without the path can fall out of both. Add an `isnull` condition when absent paths must be included. On PostgreSQL a single key compiles to `->` and a longer path to `#>`; the JSONB operator catalogue itself belongs to the PostgreSQL topic.

  • Why can't you tell SQL NULL from JSON null after loading a Django JSONField?
    Both are converted to Python `None` when the row is read. The distinction exists only in the database, so you must query for it (`data__isnull=True` versus `data=JSONNull()`). Avoiding SQL NULL with `null=False` and `default=dict` removes the ambiguity.
  • What happens with a typo such as data__owner__icontain in a Django JSONField filter?
    No error. Any unknown segment is treated as a JSON key, so the filter looks for a key named `icontain` and simply matches nothing. Test JSON filters against real data rather than trusting that the query runs.
  • When would you use KT() on a Django JSONField?
    When you need the text value of a path as an expression, for `annotate()`, `order_by()` or a text comparison. `KT("data__owner__name")` extracts the value as text, avoiding backend differences in how JSON values compare or sort.

saying these in an interview costs you the question

  • Believes data__key__isnull=True matches a key whose value is JSON null
  • Thinks filter(data=None) on a JSONField matches SQL NULL in Django 6.1
  • Expects Django to raise an error for a misspelled JSONField lookup
  • Says Python code can tell JSON null from SQL NULL after loading
  • Assumes filter() and exclude() on a JSON path always partition the table