[Django] #34144: Casting a string inside a JSONField into an integer does not work on PostgreSQL

[Django]

#34144: Casting a string inside a JSONField into an integer does not work on
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: clement- | Owner: nobody
escolano |
Type: Bug | Status: new
Component: Database | Version: 4.1
layer (models, ORM) | Keywords: postgresql,
Severity: Normal | jsonfield, cast
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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 [https://stackoverflow.com/a/25810945 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34144>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#34144: Casting a string inside a JSONField into an integer does not work on
PostgreSQL
-------------------------------------+-------------------------------------

Reporter: Clément Escolano | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: postgresql, | Triage Stage:
jsonfield, cast | Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Description changed by Clément Escolano:

Old 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 [https://stackoverflow.com/a/25810945 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.

New 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
}}}

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 [https://stackoverflow.com/a/25810945 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;
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/34144#comment:1>

[Django]

#34144: Casting a string inside a JSONField into an integer does not work on
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Clément Escolano | Owner: nobody

Type: Bug | Status: closed

Component: Database layer | Version: 4.1
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: postgresql, | Triage Stage:
jsonfield, cast | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid

Comment:

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
[https://docs.djangoproject.com/en/dev/topics/db/queries/#django.db.models.fields.json.KT
docs]), e.g.
{{{#!python
Cast(KT("data__value"), output_field=IntegerField())
}}}
on Django < 4.2 you can use the `KeyTextTransform()` expression, e.g.
{{{#!python
Cast(KeyTextTransform("value", "data"), output_field=IntegerField())
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/34144#comment:2>

[Django]

#34144: Casting a string inside a JSONField into an integer does not work on
PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Clément Escolano | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 4.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: postgresql, | Triage Stage:
jsonfield, cast | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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.

--
Ticket URL: <https://code.djangoproject.com/ticket/34144#comment:3>