CODEWITHSUNDEEP
pythonjsondjangodjango-models
söze
söze

Reputation: 570

Django filter if an optional key in json exists

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.

Upvotes: 28

Views: 18908

Answers (3)

Flimm
Flimm

Reputation: 150643

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>]>

Upvotes: 3

montxe
montxe

Reputation: 2089

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

Upvotes: 43

Moses Koledoye
Moses Koledoye

Reputation: 78556

You can exclude all NULL values and empty strings using:

queryset  = queryset.exclude(data__key1__isnull=True).exclude(data__key1__exact='')

Upvotes: 6

Related Questions