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;