I have a Model with a JSONField:

class MyModel(models.Model):
    locale_names = models.JSONField()

The shape of the JSON Field is simple: keys are language codes (en, fr...) and values are translated strings.

I'm trying to build a search query that does an unaccented icontains search on a translated value:

MyModel.objects.filter(locale_names__en__unaccent__icontains="Test")

This does not give the expected results, because Django interprets "unaccent" as a key to look up in the JSON, rather than the unaccent PostgreSQL function:

-- Expected SQL query: something like
SELECT "app_model"."*" ...
FROM "app_model"
WHERE UPPER(UNACCENT("app_model"."locale_names" ->>'en')::text)) LIKE UPPER(UNACCENT('%Test%'))
LIMIT 21

-- Actual SQL query
SELECT "app_model"."*" ...
FROM "app_model"
WHERE UPPER(("app_model"."locale_names" #>> ARRAY['en','unaccent'])::text) LIKE UPPER('%Test%')
LIMIT 21

How can I tel Django to interpret __unaccent as the PostgreSQL function rather than a JSON path?

EDIT:

• I'm using Django 3.2
• Doing __unaccent__icontains lookups on regular CharFields works as expected.

CodePudding user response：

Unfortunately, JSONField does not support unaccent lookup.

cf. documentation :

The unaccent lookup can be used on CharField and TextField:

CodePudding user response：

As a complement to @Benbb96's answer above, my workaround was to write the WHERE clause I needed using the soon-to-be-deprecated QuerySet.extra method:

MyModel.objects.extra(
    where=[
        "UPPER(UNACCENT((app_model.locale_names->>'en')::text)) LIKE UPPER(UNACCENT(%s))"
    ],
    params=("Test",)
)

As requested by the Django team, I created a ticket with them so that this use case can be addressed without QuerySet.extra().