id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27693	Case insensitive lookups on JSONField nested values	Harris Lapiroff	Simon Charette <charette.s@…>	"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 `HStoreField`s. 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
}}}"	Bug	closed	contrib.postgres	dev	Normal	fixed			Ready for checkin	1	0	0	0	0	0