NOT query using JSONField but including nulls

I have a JSONField (with default=dict, not sure if this is relevant in this case) in my model called data and I’m using Postgres. If I do ~Q(data__city='Atlantis') the query excludes not only rows where city is Atlantis, but also rows where data has no city.

Is this expected behavior? I think so, because the generated query is very simple, so it seems a Postgres behavior.

If so, what’s the recommended way to also include rows without city? I did ~(Q(data__city='Atlantis') & Q(data__has_key='city')). But it’s a bit verbose. So I would like to know if there is a simpler way.

Thanks for your attention.

The JsonField doc covers a few of the scenarios that you’re describing.

Thanks for your response, Leandro!

As far as I can see this is the most relevant part to my question:

Due to the way in which key-path queries work, exclude() and filter() are not guaranteed to produce exhaustive sets. If you want to include objects that do not have the path, add the isnull lookup.

And this seems to answer my first question, that it’s indeed an expected behavior.

To use the isnull as the paragraph suggests would be something like ~Q(data__city='Atlantis') | Q(data__isnull='city')? But I can’t see a clear advantage in this strategy compared to the has_key I’m using. In reality, the way I’m building queries in this particular case makes the has_key strategy much simpler to implement.

Almost, it would be:

~Q(data__city='Atlantis') | Q(data__city__isnull=True)

Ops. My confusion, sorry. And thanks!