Django djsonfield Decimal stored as string in expressions

Question

I have a JSONField with some financial data. I cannot store it as a float, because I need precision, so I store it as a string.

My model looks like this

class Finance(models.Model)
    bill_data = JSONField(
        verbose_name=_("Bill data"),
        default=dict,
        blank=True,
    )

then I save

bill_data = dict(paid=str(some_decimal_amount))

Finance.objects.create(bill_data=bill_data)

I try to use Cast to convert it back to Decimal, because I need to use expressions,

Finance.objects.all().annotate(paid=Cast('bill_data__paid', DecimalField()))

I get an error

    return self.cursor.execute(sql, params)
django.db.utils.DataError: invalid input syntax for integer: "none"
LINE 1: ...der"."bill_data", ("myapp_finance"."bill_data")::numeric(No...

Does anyone know how to use str from JSONField in expressions or how to handle Decimal in JSONField correctly?

why dont you run the query normally without casting and then it to desired format? — Neeraj, Apr 09 '21 at 13:14
it is just simple query to picture what I am doing. The `bill_data` field is in my code much more expanded and I need to perform query like sum, average etc using values stored in `bill_data` and do it in single query if possible to avoid race conditions — alis01, Apr 09 '21 at 13:35

score 3 · Accepted Answer · answered Nov 18 '21 at 21:08

So today I struggled with exactly this. With help, we managed to get it working.

from django.db.models.functions import Cast
from django.db.models.fields.json import KeyTextTransform
from django.db.models import JSONField, DecimalField

from django.db import models


class Finance(models.Model):
    bill_data = JSONField(
        verbose_name="Bill data",
        default=dict,
        blank=True,
    )

OK so first I'm assuming that bill_data__paid is not Null anywhere. If it is, you should first do a:

Finance.objects.filter(bill_data__paid__isnull=False)

to make sure you are not casting any Null fields.

Ok, then let's try what you did:

Finance.objects.annotate(
    paid=Cast("bill_data__paid", output_field=DecimalField()),
)

But we get an error, something along the lines of:

invalid input syntax for type integer: "none" LINE 1: # ...("myapp_finance"."bill_data" -> 'paid') AS numeric(No......

Ok that's not great. What can we do now then? Well, perhaps we need to specify the number of decimal places and max digits for the Decimal field and you are right that we do.

Finance.objects.annotate(
    paid=Cast("bill_data__paid", output_field=DecimalField(max_digits=6, decimal_places=2)),
)

But does that work? Sadly, no. We are now getting the error

cannot cast jsonb string to type numeric

Hmmm okay. not great but at least it's a different error. The issue is that we have a jsonb string. Let's make it a text text string (for lack of better description)

Finance.objects.annotate(
    paid=Cast(KeyTextTransform("paid", "bill_data"), output_field=DecimalField(max_digits=6, decimal_places=2))
)

and now, it will work.

So we have cast our jsonb string to text, we have then cast it to decimal (and you have to specify the number of decimal places and max digits).

The end :)

Thank you! I think I did some workaround back then, but for sure it will help people having the same problem — alis01, Nov 19 '21 at 11:54

Django djsonfield Decimal stored as string in expressions

1 Answers1