Casting a string inside a JSONField into an integer does not work on PostgreSQL

[Clément Escolano]

Hello

When casting an integer represented as a string inside a JSONField on PostgreSQL, there is the following error:

django.db.utils.DataError: cannot cast jsonb string to type integer

It works with a SQLite database.

To reproduce the error, you can create a simple model with a JSONField:

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

MyModel.objects.create(data={"value": "3"})

MyModel.objects.annotate(actual_value=Cast("data__value", output_field=IntegerField())).first()

The produced query is the following:

SELECT "myapp_mymodel"."id",
       "myapp_mymodel"."data",
       (("myapp_mymodel"."data" -> 'value'))::integer AS "actual_value"
FROM "myapp_mymodel"
LIMIT 1;

This ​stackoverflow question hints that adding an extra ">" after "->" fixes it and indeed the following query returns the correct result:

SELECT "myapp_mymodel"."id",
       "myapp_mymodel"."data",
       (("myapp_mymodel"."data" ->> 'value'))::integer AS "actual_value"
FROM "myapp_mymodel"
LIMIT 1;

[Mariusz Felisiak]

I don't think there is much we can do 🤔 as this is a PostgreSQL caveat. On Django 4.2+ you will be able to use the KT() expression (see ​docs), e.g.

Cast(KT("data__value"), output_field=IntegerField())

on Django < 4.2 you can use the KeyTextTransform() expression, e.g.

Cast(KeyTextTransform("value", "data"), output_field=IntegerField())

[Clément Escolano]

Thank you for the information and the method on how to solve my problem.
It would have been great that django automatically uses this method (when using the Cast method, I expect the casting to not have issues) but I assume this is for a good reason.