Case insensitive lookups on JSONField nested values

[Harris Lapiroff]

Currently JSONField doesn't support case-insensitive lookups such as iexact. This is not documented as working, so I'm marking this as a new feature, but it's something I think Django can and should support.

Attempting to use an iexact lookup (e.g., OrganizationPerson.objects.filter(data__field2__iexact='Ethelfort')) results in a SQL error:

django.db.utils.ProgrammingError: function upper(jsonb) does not exist
LINE 1: ...st" FROM "organizations_organizationperson" WHERE UPPER("org...
                                                             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

I believe this is just a case of misplaced parens and quotes. Similar functionality (though still not documented) does work on HStoreFields. Here's an example of the successful SQL Django generates for an iexact lookup on an HStoreField.

SELECT "organizations_organizationperson"."id", "organizations_organizationperson"."organization_id", "organizations_organizationperson"."person_id", "organizations_organizationperson"."data", "organizations_organizationperson"."test" FROM "organizations_organizationperson" WHERE UPPER(("organizations_organizationperson"."test" -> 'foo')::text) = UPPER('Bar') LIMIT 21

And here's the unsuccessful SQL Django generates for an iexact lookup on a JSONField, which generates the error above:

SELECT "organizations_organizationperson"."id", "organizations_organizationperson"."organization_id", "organizations_organizationperson"."person_id", "organizations_organizationperson"."data", "organizations_organizationperson"."test" FROM "organizations_organizationperson" WHERE UPPER("organizations_organizationperson"."data" -> 'field2'::text) = UPPER('Ethelfort') LIMIT 21

I don't have an ORM-level fix for correcting the query, but a proper query that works as expected just includes a couple more parens and quotes in the WHERE clause:

SELECT "organizations_organizationperson"."id", "organizations_organizationperson"."organization_id", "organizations_organizationperson"."person_id", "organizations_organizationperson"."data", "organizations_organizationperson"."test" FROM "organizations_organizationperson" WHERE UPPER(("organizations_organizationperson"."data" -> 'field2')::text) = UPPER('"Ethelfort"') LIMIT 21

[Tim Graham]

Did you reproduce this on master? I thought it might be fixed by #27257 -- Fixed builtin text lookups on JSONField keys.

[Stephen Burrows]

Just tested this on master, and I can confirm that the iexact lookup works - except that you need to add the quotes for the query in yourself. i.e.

>>> x = OrganizationPerson.objects.first()
>>> x.data
{'field2': 'Ethelfort'}
>>> OrganizationPerson.objects.filter(data__field2__iexact='Ethelfort')
<QuerySet []>
>>> OrganizationPerson.objects.filter(data__field2__iexact='"Ethelfort"')
<QuerySet [<OrganizationPerson: OrganizationPerson object>]>

Knowing how the SQL ends up looking, this sort of makes sense to me - but if I didn't know that from this ticket I would be completely lost.

[Simon Charette]

It looks like the iexact case was overlooked by #27257. It believe it would be a simple matter of defining a KeyTransformIExact(KeyTransformTextLookupMixin, builtin_lookups.IExact) subclass and registering it as a KeyTransform lookup. See cecef94275118dc49a1b0d89d3ca9734e2ec9776.

[Simon Charette]

​PR

[Simon Charette <charette.s@…>]

In d9767602:

Fixed #27693, Refs #27257 -- Fixed iexact lookup on JSONField keys.

Thanks Harris Lapiroff for the report.