Discussion on: Aggregation in Django JSONFields

[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'

[Monika Suresh Gujar]

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'