Opened 2 years ago

Last modified 2 years ago

#34144 closed Bug

Casting a string inside a JSONField into an integer does not work on PostgreSQL — at Initial Version

Reported by: Clément Escolano Owned by: nobody
Component: Database layer (models, ORM) Version: 4.1
Severity: Normal Keywords: postgresql, jsonfield, cast
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

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" -> '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" ->> 'value'))::integer AS "actual_value"
FROM "myapp_mymodel"
LIMIT 1;

The code is already working on SQLite.

Change History (0)

Note: See TracTickets for help on using tickets.
Back to Top