Aggregation on json field float value

Using Django Using the ORM
1

I was trying to figure out how to aggregate values from a json field in Django and I found the following error TypeError: the JSON object must be str, bytes or bytearray, not float.

I didn’t see any documentation about aggregating json values in Making queries | Django documentation | Django so, I’m not sure if this is even possible. I expected that something like the following will work. Am I doing something wrong?

# models.py
class Example(models.Model):
    data = models.JSONField()

# app/management/commands/example.py
from django.core.management.base import BaseCommand
from django.db.models import Sum
from app.models import Example

class Command(BaseCommand):

    def handle(self, *args, **options):
        # insert data
        example = Example(data={"float": 4.5})
        example.full_clean()
        example.save()

        # query data

        print(Example.objects.filter(data__float__gt=4)) # <- this works
        Example.objects.aggregate(Sum("data__float")) # <- this fails

I would expect that if a filter like data__float__gt can decode a float from database without a problem, because Django is in charge of encoding and decoding, the aggregation query will do as well.

Full stack trace:

Traceback (most recent call last):
  File "/home/lapisoft/projects/jsonerror/jsonerror/manage.py", line 22, in <module>
    main()
  File "/home/lapisoft/projects/jsonerror/jsonerror/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/home/lapisoft/projects/jsonerror/jsonerror/app/management/commands/example.py", line 18, in handle
    Example.objects.aggregate(Sum("data__float"))
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/db/models/query.py", line 592, in aggregate
    return self.query.chain().get_aggregation(self.db, kwargs)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/db/models/sql/query.py", line 559, in get_aggregation
    result = next(compiler.apply_converters((result,), converters))
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/db/models/sql/compiler.py", line 1500, in apply_converters
    value = converter(value, expression, connection)
  File "/home/lapisoft/.cache/pypoetry/virtualenvs/jsonerror-FRgQOuu0-py3.10/lib/python3.10/site-packages/django/db/models/fields/json.py", line 94, in from_db_value
    return json.loads(value, cls=self.decoder)
  File "/usr/lib/python3.10/json/__init__.py", line 339, in loads
    raise TypeError(f'the JSON object must be str, bytes or bytearray, '
TypeError: the JSON object must be str, bytes or bytearray, not float

Using:

  • python 3.10.12
  • django 4.2
  • mysql 8.4
2

Hello there!

A possible workaround for this is to annotate the value first and using the annotation to aggregate? Like:

from django.db.models import F, FloatField
Example.objects.annotate(val=F("data__float", output_field=FloatField())).aggregate(Sum("val"))

Haven’t tested this though

3

@leandrodesouzadev’s answer should work but you can pass output_field directly to Sum with the same results.

Example.objects.aggregate(
    Sum("data__float", output_field=FloatField())
)

What’s happening here is that since JSONField are schemaless the ORM has no way to know that data__float is actually a JSON float. You can to tell it is the case otherwise it will try to json.loads the data returned from the database.

2 Likes
4

i think your database in that field loged 123.123,
but json field need “123.123”.

maybe it’s the reason.

you can

  • choice coutput filed => charettes solution
  • chane database value => maybe raise error in call model object
  • change field => if you loged int data