10

As far as I know you can't use __isnull lookups on django native JSONField. On the Internet I found this inactive issue. As possible workaround we can of course use these hacks:

  1. model.objects.filter(field__contains={'key': None}), which isn't so flexible since you might need to query multiple keys or whatever.

  2. model.objects.exclude(field__key=True).exclude(field__key=False), which is hacky and works only for boolean data.

I hope there is a better way ((c) Raymond Hettinger) of doing this. Any advises will be appreciated. For now, I will go with the first approach

valignatev
  • 6,020
  • 8
  • 37
  • 61

1 Answers1

6

According to this (see the last, closing comment) the following should work model.objects.filter(field__key=None) (But obviously you should use the Django version with the fix).

The django docs

warn that

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.

and here they are

Amin Mir
  • 640
  • 8
  • 15
Bob
  • 5,809
  • 5
  • 36
  • 53
  • Seems like `field__key__isnull=True` should work as well. Finally, after all these years, this question has an answer. Thanks! – valignatev Apr 21 '18 at 14:50
  • I cannot get `field__key__isnull=True` to work. Looking at the code changes in the above link, the `JSONExact` lookup was added so for me `field__key__exact=None` is working too. – djbp Mar 13 '19 at 08:17
  • Queries on JSONFields can easily be a place for bugs to hide due to the dynamic key lookup that doesn't raise errors. For example, running `model.objects.filter(field__is_null=True)` or `model.objects.filter(field__is_null=False)` mistakenly using `is_null` instead of `isnull` will always return an empty queryset with no errrors because it's looking for a key called `is_null`. – Ben Sturmfels Sep 08 '22 at 02:46
  • `.exclude(field__key__isnull=True)` is still not working in Django 4.1 but `__exact=None` works – Tim Richardson May 24 '23 at 06:44