How do I write a Django ORM query that returns a match based on an intersection of lists?

If I have, for example:

from django.contrib.postgres.fields import JSONField

class MyModel(models.Model):
    data = JSONField()

GIVEN a model instance obj1, where data == ['apple', 'banana', 'orange']

GIVEN a model instance obj2, where data == ['banana', 'orange']

GIVEN a model instance obj3, where data == ['apple']

How can I write an ORM query that returns all model instances whose data includes at least one item from a given list, ['apple', 'strawberry']? It should return obj1 and obj2 because both of those objects include 'apple', despite the argument including 'strawberry', which is not represented in any of the objects.

You can probably use __has_any_keys [Django-doc]:

MyModel.objects.filter(data__has_any_keys=['apple', 'strawberry'])

But in a relational database where the structure of the data is fixed, you should not treat JSON blobs as non-atomic values [django-antipatterns]. Disclosure: I am the author of that page.

Back to Top