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
Collapse
 
reaganscofield profile image
Reagan Scofield • • 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 ?

Collapse
 
saschalalala profile image
Sascha •

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?

Collapse
 
reaganscofield profile image
Reagan Scofield • • 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
    },
Thread Thread
 
saschalalala profile image
Sascha • • 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

Collapse
 
monikagujar profile image
Monika Suresh Gujar •

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'
Enter fullscreen mode Exit fullscreen mode
Collapse
 
monikagujar profile image
Monika Suresh Gujar • • 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'
Enter fullscreen mode Exit fullscreen mode
Collapse
 
arnababstentia profile image
arnab-abstentia •

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 !!!

Collapse
 
saschalalala profile image
Sascha •

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.