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.
Note:
See TracTickets
for help on using tickets.