I have a model X with a custom json fied data which may or may not have these keys: key1, key2, key3.
I need a way to filter all instances with key1.
I have tried doing this:
queryset = X.objects.all()
queryset = queryset.annotate(
has_key1=(
Case(
When(~Q(data__key1__in=[None, '']), then=True),
default=False,
output_field=models.BooleanField())
)
)
But queryset.filter(has_key1=True) returns nothing yet there are some records with key1.
A direct filter: queryset.filter(data__key1__in=[1]) works okay.
Anyone with a solution to this? Thank you.
You can use has_key on JSONField.
JSONField and HStoreField share this look up - docs.
X.objects.filter(data__has_key='key_1')
Here is an example based on my project, I used has_key on JSONField in FilterSet (django_filters.rest_framework), to implement filtering by key (if key doesn't exists returns 0 results):
filters.py:
class XFilter(FilterSet):
data_search_by_key = django_filters.CharFilter(method='filter_data_key',
help_text='Search by key')
def filter_data_key(self, qs, name, value):
qs = X.objects.filter(data__has_key=value)
return qs
You can exclude all NULL values and empty strings using:
queryset = queryset.exclude(data__key1__isnull=True).exclude(data__key1__exact='')
Newer versions of Django ship with JSONField for multiple databases, not just PostgreSQL, which you can import like this:
from django.models import JSONField
The Django documentation contains instructions on how to query JSONField. In particular, to answer your question, you can see if a JSONField has a particular key by using __has_key, like this:
>>> Dog.objects.create(name='Meg', data={'owner': 'Bob'})
<Dog: Meg>
>>> Dog.objects.filter(data__has_key='owner')
<QuerySet [<Dog: Meg>]>