Django filter if an optional key in json exists

Question

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.

Answer 1

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

Answer 2

You can exclude all NULL values and empty strings using:

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