Hello there, I'm not sure where you determined the SQL that caused the error but I'm pretty sure this was caused by "my_model"."data" -> 'name' not being wrapped in a parenthesis before the ::text cast.
This has been fixed in Django 1.11[0]. Cheers, Simon [0] https://github.com/django/django/commit/2eb7d6e6d41480f21305fc6abe2f1a443491ddec#diff-57ceaae67721b3f8d5729222c032af8eR109 Le dimanche 9 avril 2017 21:48:57 UTC-4, cofiem a écrit : > > The issue I'm facing is similar to these tickets: > > - Case insensitive lookups on JSONField nested values > https://code.djangoproject.com/ticket/27693 > - Use the ->> operator when filtering builtin text lookups on > JSONField keys https://code.djangoproject.com/ticket/27257 > - Document how to do a substring search in JSONField > https://code.djangoproject.com/ticket/26511 > > I have a JsonField in a model > > data = JSONField(null=True, blank=True) > > > It contains this json: > > {"name":"Hello"} > > > My filter is: > > models.MyModel.objects.filter(data__name__icontains='el') > > > I expect the query to return the objects that have json where the "name" > contains 'el' (case-insensitive). > > Instead, I get this: > > ProgrammingError at /url/ >> function upper(jsonb) does not exist LINE 1: ... >> UPPER("my_model"."data"... >> HINT: No function matches the given name and argument types. You might >> need to add explicit type casts. > > > The SQL that causes the error is > > WHERE UPPER(("my_model"."data" -> 'name')::text) LIKE UPPER(%el%) > > > The working SQL is (note added quotes around LIKE text): > > WHERE UPPER(("my_model"."data" -> 'name')::text) LIKE UPPER('%el%') > > > Is there a fix for this? Am I missing something? > Any help would be appreciated. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/a4cab7dd-027d-4eea-b55c-55348b24b78e%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
