JSONField filter() and JSONField exclude() are sometimes not inverses of each other

[Mikuael]

Filtering based on a JSONField key-value pair seems to have some unexpected behavior when involving a key that not all records have. Strangely, filtering on an optional property key will not return the inverse result set that an exclude on the same property key will return.

In my database, I have:

• 2250 total records
• 49 records where jsonfieldname = {'propertykey': 'PropertyValue'}
• 296 records where jsonfieldname has a 'propertykey' key with some other value
• 1905 records where jsonfieldname does not have a 'propertykey' key at all

The following code:

q = Q(jsonfieldname__propertykey="PropertyValue")

total_records = Record.objects.count()
filtered_records = Record.objects.filter(q).count()
excluded_records = Record.objects.exclude(q).count()
filtered_plus_excluded_records = filtered_records + excluded_records
    
print('Total: %d' % total_records)
print('Filtered: %d' % filtered_records)
print('Excluded: %d' % excluded_records)
print('Filtered Plus Excluded: %d' % filtered_plus_excluded_records)

Will output this:

Total: 2250
Filtered: 49
Excluded: 296
Filtered Plus Excluded: 345

It is surprising that the filtered+excluded value is not equal to the total record count. It's surprising that the union of a expression plus its inverse does not equal the sum of all records. I am not aware of any other queries in Django that would return a result like this. I realize adding a check that the key exists would return a more expected results, but that doesn't stop the above from being surprising.

I'm not sure what a solution would be - either a note in the documentation that this behavior should be expected, or take a look at how this same expression is applied for both the exclude() and filter() queries and see why they are not opposites.

[Carlton Gibson]

Hi Mikuael. Thanks for the report. Yes, let's take this as a Documentation issue: it's expected behaviour but worth calling out explicitly.

(If you wanted to add a few additional test cases for exclude that might be cool too, documenting and verifying behaviour, on each of the backend DBs.)

[Johnny Pribyl]

I can look at this one! It looks pretty straightforward.

[Sage Abdullah]

I think we can change the existing Oracle note about exclude() (​here) to make it more general (not just with None rhs) and remove the Oracle users note title to just Note or Warning. Like what Carlton said, it would be great if we also add test cases for that. I think we should also explicitly say that in order to get the proper inverse of filter(), users should also consider querying with __isnull.

[Johnny Pribyl]

​https://github.com/django/django/pull/13367

[Johnny Pribyl]

Sage, sorry just now saw your comment! I wound up adding a new section but am happy to combine the two if you think that makes more sense.

[Johnny Pribyl]

Looks like there are some changes to be made - I didn't realize quite how dependent this behavior was on db backend! I will have to look at this a bit more.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 94e22381:

Refs #31894 -- Added tests for JSONField key lookups with QuerySet.exclude().

[Mariusz Felisiak <felisiak.mariusz@…>]

In 17407eca:

Fixed #31894 -- Added note about using JSONField key lookups with QuerySet.exclude() in docs.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 3fc63624:

[3.1.x] Fixed #31894 -- Added note about using JSONField key lookups with QuerySet.exclude() in docs.

Backport of 17407eca59b0572228067cdee51433f49a1e0adb from master