Question 115117 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Looking up value in JSONField with unaccent and icontains

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.
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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().


  2. 0

    Unfortunately, JSONField does not support unaccent lookup.

    cf. documentation :

    The unaccent lookup can be used on CharField and TextField:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search