Django ORM Cast() returning double quoted string from JSON field

Question

I need to annotate a value that is saved in a json field in the same model. (Not the smartest but it is what it is).

I am annotating the value as such:

class SomeModel(BaseModel):
    reference_numbers = JSONField(blank=True, null=True)


SomeModel.objects.annotate(
    reference=Cast(
        F("reference_numbers__some_id"),
        output_field=models.CharField(),
    )
)

I need it to be cast to text/char in the query because a subsequent search will only work on text/char (trigram similarity).

It works, sort of, but the result adds an extra quote to my string. Like so:

queryset[0].reference -> '"666999"'

Any ideas on how to get the correct string from the query?

I've also tried using just an ExpressionWrapper with the output field but since it doesnt cast the type in the SQL the code breaks afterwards when trying to execute the search because it uses the jsonb field still.

Answer 1

I was having the same issue and after some research I found out that the right way to do this seems to be a KeyTextTransform. The following should probably work for you:

from django.db.models.fields.json import KeyTextTransform

SomeModel.objects.annotate(
    reference=KeyTextTransform("some_id", "reference_numbers")
)

If your access pattern becomes more complicated you might have to nest them, see here.

There's also a query expression called KT that just arrived with Django 4.2 and allows deeper access patterns.