loading...

re: Aggregation in Django JSONFields VIEW POST

TOP OF THREAD FULL DISCUSSION
re: 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 wor...
 

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?

 

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

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

code of conduct - report abuse