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

Question

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 obj3 because both of those objects include 'apple', despite the argument including 'strawberry', which is not represented in any of the objects.

Don't use a JSONFIeld...

willeM_ Van Onsem
– willeM_ Van Onsem

2025-06-11 18:06:23 +00:00
Commented Jun 11 at 18:06 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Jun 11 at 18:06

willeM_ Van Onsem · Accepted Answer · 2025-06-11 18:13:09Z

1

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.

answered Jun 11 at 18:13

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dane Iracleous Jun 11 at 18:47

__has_any_keys seems to operate on the keys though. I'm looking for something that operates on just a basic list of strings. data is a list, like ['apple', 'banana', 'orange'], and not a dictionary like {'apple': 1, 'banana': 2, 'orange': 3}

willeM_ Van Onsem Jun 11 at 18:51

: that's what I first though too, but the documentation also shows it for an array: postgresql.org/docs/9.5/functions-json.html (well for ?& but it is the same concept).

Dane Iracleous Jun 11 at 18:59

You're right. This works. Thanks

willeM_ Van Onsem Jun 12 at 11:16

: I opened a Django ticket to include it in the documentation.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related