DEV Community

Aggregation in Django JSONFields

Sascha on May 25, 2019

This is my first post on dev.to and I think about it more as a note for myself and perhaps someone finds this as interesting as I do. Wha...

Read full post

Reagan Scofield • Sep 8 '19 • Edited

I spend couple hours trying to figure the right ORM Algorithms but I find your article so clean however I followed step by step my code doesn't work the way I expected it to work, the COUNT return result and SUM return None type it does seems like it doesn't getting a value of JSON keys can you please assist ? and what version of Django are you using for this article ?

Sascha • Sep 9 '19

I am using Django in its most recent version which is 2.2.5.

Can you describe your data structure in more detail in order for me to understand your problem better?

Perhaps you could also provide some example json data?

Reagan Scofield • Sep 9 '19 • Edited

Here are my code and I used Django 2.2.2, when I query data JSON value does come as None type and the count does bring value

from django.db import models
import uuid
from django.contrib.postgres.fields import JSONField
from django.contrib.postgres.fields.jsonb import KeyTextTransform
from django.db.models import Count, Sum
from django.db.models.functions import Cast
from django.db.models import FloatField
from django.db.models.functions import TruncDay       
from django.db.models import Q
from django.db.models.expressions import RawSQL


class Manager(models.Manager):
    def counts_and_sums(self, date_from, date_to):
        return (
            DataPoint.objects
                .annotate(day=TruncDay('datetime')).values('day')
                .annotate(count=Count('datetime'))
                .annotate(observed_irradiation_total = Sum(Cast(KeyTextTransform('irradiation', 'observed'), FloatField())) )
                .annotate(observed_energy_total = Sum(Cast(KeyTextTransform("energy", 'observed'), FloatField())))
                .annotate(expected_irradiation_total = Sum(Cast(KeyTextTransform('irradiation', 'expected'), FloatField())) )
                .annotate(expected_energy_total = Sum(Cast(KeyTextTransform('energy', 'expected'), FloatField())) )
                .values('day', 'count', 'observed_irradiation_total', 'observed_energy_total', 'expected_irradiation_total', 'expected_energy_total')
                .filter(Q(datetime__range=[date_from, date_to]))
        )

class DataPoint(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    plant_id = models.ForeignKey(Plant, blank=True, null=True, on_delete=models.CASCADE)

    datetime = models.DateTimeField()
    expected = JSONField(blank=True, null=True)
    observed = JSONField(blank=True, null=True)
    objects = Manager()

    def __str__(self):
        return f"{self.datetime}"

query

        objectData = DataPoint.objects.counts_and_sums(date_from, date_to)

        for data in objectData:
            print(data)

my response

{'day': datetime.datetime(2019, 9, 8, 0, 0, tzinfo=<UTC>), 'count': 747, 'observed_irradiation_total': None, 'observed_energy_total': None, 'expected_irradiation_total': None, 'expected_energy_total': None}

and my object looks like this when I query it with normal DataPoint.objects.all()

 {
        "id": "742372b1-56b3-4d97-a7b1-a82eb205bcdf",
        "datetime": "2019-09-08T19:02:00Z",
        "expected": "{\"energy\": 90.78559770167864, \"irradiation\": 30.085498370965905}",
        "observed": "{\"energy\": 90.78559770167864, \"irradiation\": 30.085498370965905}",
        "plant_id": null
    },

Sascha • Sep 10 '19 • Edited

Unfortunately, all I can say is, it works for me.

Here is my example code that I used to create some example data:

import datetime as dt # don't care about timezones right now

expected = {"energy": 90.78559770167864, "irradiation": 30.085498370965905}
observed = {"energy": 10.78559770167864, "irradiation": 20.085498370965905}

today = dt.date.today()

# Just create some identical objects, I don't think that this matters right now

DataPoint.objects.create(datetime=today, expected=expected, observed=observed)
DataPoint.objects.create(datetime=today, expected=expected, observed=observed)
DataPoint.objects.create(datetime=today, expected=expected, observed=observed)
DataPoint.objects.create(datetime=today, expected=expected, observed=observed)

date_from = today - dt.timedelta(days=4)
date_to = today + dt.timedelta(days=3)

object_data = DataPoint.objects.counts_and_sums(date_from, date_to)

print(object_data)
<QuerySet [{'day': datetime.datetime(2019, 9, 10, 0, 0, tzinfo=<DstTzInfo 'Europe/Berlin' CEST+2:00:00 DST>), 'count': 4, 'observed_irradiation_total': 80.3419934838636, 'observed_energy_total': 43.1423908067146, 'expected_irradiation_total': 120.341993483864, 'expected_energy_total': 363.142390806715}]>

And just for the records, my version, taken from the same shell:

import django
print(django.VERSION)
(2, 2, 2, 'final', 0)

The only thing I changed in your code, is that I removed the plant_id ForeignKey

Monika Suresh Gujar • May 10 '22

I am using Django==3.0.5 with mongodb. There is one model UploadCableTrayData and I want consolidated data from it.

For example:

I have multiple entries stored in db like below.

{
_id: ObjectId('61ae186196f098bb40131d29'),
order_upload_id: 1,
Type: '{"key": "type", "value": "pct"}',
Length: '{"key": "length", "value": "1"}',
project_id: 1
}

{
_id: ObjectId('61aed76746ad80bbef86213b'),
order_upload_id: 2,
Type: '{"key": "type", "value": "pct"}',
Length: '{"key": "length", "value": "120"}',
project_id: 2
}

{
_id: ObjectId('61ae186196f098bb40131d29'),
order_upload_id: 1,
Type: '{"key": "type", "value": "lct"}',
Length: '{"key": "length", "value": "11"}',
project_id: 1
}

{
_id: ObjectId('61aed76746ad80bbef86213b'),
order_upload_id: 2,
Type: '{"key": "type", "value": "bct"}',
Length: '{"key": "length", "value": "120"}',
project_id: 2
}

What I want is, summation of Length-->value, by grouping the data of same Type

Final result should look like this

[
{Type: "pct", Length: 121},
{Type: "bct", Length: 120},
{Type: "lct", Length: 11}
]

I tried multiple solutions but none worked for me.

1.
UploadCableTrayData.objects.annotate(length_value_total=Sum(Cast(KeyTextTransform('value', 'Length'),FloatField()))).values('length_value_total').filter(creator_id_in=selected_users).filter(date_added_range=(selected_start_date, selected_end_date))

2.
UploadCableTrayData.objects.annotate(val=KeyTextTransform('value', 'value__Length')).aggregate(Sum('val'))

3.
UploadCableTrayData.objects.annotate(s=RawSQL("((Length->>'value')::int)",(0,))).aggregate(sold=Sum('s'))

Model:

class UploadCableTrayData(BaseModel, models.Model):
"""
Model to store Cable Tray data

"""
order_upload_id = models.AutoField(primary_key=True)
Type = jsonfield.JSONField(null=True, blank=True)
Length = jsonfield.JSONField(null=True)
project = models.ForeignKey('project.Project', on_delete=models.DO_NOTHING)

def __str__(self):
    return str(self.order_upload_id)

class Meta:
    db_table = 'UploadCableTrayData'

Monika Suresh Gujar • Jun 10 '22 • Edited

After doing the same as mentioned in this article, I am getting below in response.

views.py

data123 = UploadCableTrayData.objects.counts_only()

models.py

class UploadCableTrayDataManager(models.Manager):
def counts_only(self):
return (UploadCableTrayData.objects.annotate(thickness=Cast(KeyTextTransform("value", "Thickness"), models.FloatField())).values("Thickness", "Selection").annotate(thickness_count=Count("Thickness"), running=Sum(Cast(KeyTextTransform("value", "Length"), models.FloatField()))).order_by())

class UploadCableTrayData(BaseModel, models.Model):
"""
Model to store Cable Tray data

"""
order_upload_id = models.AutoField(primary_key=True)
Order_number = JSONField(null=True, blank=True)
Type = JSONField(null=True, blank=True)
Selection = JSONField(null=True, blank=True)
Height = JSONField(null=True, blank=True)
Width = JSONField(null=True)
Box_width = JSONField(null=True)
Box_height = JSONField(null=True)
Length = JSONField(null=True)
Inner_bend1 = JSONField(null=True, blank=True)
Inner_bend2 = JSONField(null=True, blank=True)
Thickness = JSONField(null=True, blank=True)
Rung_width = JSONField(null=True, blank=True)
Rung_height = JSONField(null=True, blank=True)
Distance_between_rungs = JSONField(null=True, blank=True)
project = models.ForeignKey('project.Project', on_delete=models.DO_NOTHING)

objects = UploadCableTrayDataManager()

def __str__(self):
    return str(self.order_upload_id)

class Meta:
    db_table = 'UploadCableTrayData'

arnab-abstentia • Jun 22 '19

Facing a similar kind of problem in my office project. Just going through your code and implemented it and everything work's fine. Thanks for sharing !!!

Sascha • Jun 23 '19

I love JSONFields. There are so many things that you can do with them (ArrayFields, also) which are somewhat counter intuitive compared to "classical rdbms thinking" but make some problems so much easier.