How to query a JSONField comprising a list of values in DJANGO

Question

I am using JSONField to store the configuration parameter(user_types) as a list as follows:

["user_type1", "user_type2", "user_type3"]

How to query to filter elements of type "user_type1"? The following query is not working:

rows=ConfigUserTable.objects.filter(user_types__in=["user_type1"])

Thanks

Answer 1

Use the contains lookup, which is overridden on JSONField. For example, the following may work:

ConfigUserTable.objects.filter(user_types__contains="user_type1")

However, this might depend on how you are storing JSON data in the field. If you are storing the data as a dict, querying on that key will certainly work. I.e. data in this format in the field user_types:

{"types": ["user_type1", "user_type2", "user_type3"]}

could be queried like so:

ConfigUserTable.objects.filter(user_types__types__contains="user_type1")

Reference: https://docs.djangoproject.com/en/dev/topics/db/queries/#std:fieldlookup-jsonfield.contains

Answer 2

__contains works. Adding an answer as the current one is a guess with errors, and this isn't covered in the Django docs. Also there is a twist for __icontains.

>>> for i in MyModel.objects.all():
>>>     i.aliases = None  # JSONField(null=True)
>>>     i.save()
>>> i.aliases = ['a', 'b', 'abc']
>>> i.save()
>>> MyModel.objects.filter(aliases__contains='a').exists()
True
>>> MyModel.objects.filter(aliases__contains='ab').exists()
False
>>> MyModel.objects.filter(aliases__contains='abc').exists()
True
>>> MyModel.objects.filter(aliases__contains='A').exists()
False
>>> MyModel.objects.filter(aliases__icontains='A').exists()
True
>>> MyModel.objects.filter(aliases__icontains='AB').exists()
True  

That last result surprised me because the i in __icontains usually means case-insentive, but in this case it means string entries are case-insensitive and partially matched.

This is because Django uses LIKE for i-prefixed lookups:

>>> MyModel.objects.filter(aliases__contains='ab').exists()
False
>>> print(MyModel.objects.filter(aliases__contains='ab').query)
...WHERE "metadata_livelihoodcategory"."aliases" @> '"ab"'...

>>> MyModel.objects.filter(aliases__icontains='ab').exists()
True
>>> print(MyModel.objects.filter(aliases__icontains='ab').query)
...WHERE UPPER("metadata_livelihoodcategory"."aliases"::text) LIKE UPPER(%ab%)...

Use of LIKE here is a tiny bit hacky, but very useful, and if I had to choose, I'd choose this behaviour.

However, look again at that __icontains SQL:

>>> MyModel.objects.filter(aliases__icontains='["A", "AB').exists()
True
>>> MyModel.objects.filter(aliases__icontains='", "').exists()
True

Better SQL is possible (thanks):

>>> select count(*) from app_mymodel where
...     upper(aliases::text)::jsonb @> upper('"AB"')::jsonb;
0
>>> select count(*) from app_mymodel where 
...     upper(aliases::text)::jsonb @> upper('"A"')::jsonb;
1
>>> select count(*) from app_mymodel where 
...     upper(aliases::text)::jsonb @> upper('", "')::jsonb;
0

How would you name the lookup for this "a list contains a string which case-insensitive contains"? __iccontains? Postgresql only?

One last thing. __contains does not seem to work when within a dict.

>>> i.aliases = {"types": ["user_type1", "user_type2", "user_type3"]}
>>> i.save()
>>> MyModel.objects.filter(aliases__contains="types").exists()
True
>>> MyModel.objects.filter(aliases__types__contains="user_type1").exists()
False
>>> MyModel.objects.filter(aliases__types__icontains="user_type1").exists()
False
>>> MyModel.objects.filter(aliases__types__0__contains="user_type1").exists()
False
>>> MyModel.objects.filter(aliases__types__0__icontains="user_type1").exists()
False

Django 4.2.7 on Postgresql. aliases is a standard JSONField with null=True.