in django ,how to sum a int field and two float field in jsonb using postgresql

Question

I have a model like this:

class Priority(models.Model):
    base = models.FloatField(default=0)
    job = models.JSONField()
    users = models.JSONField()

and both job and users are similar.

like job = {'a':1,'b':2}, user = {'c':3,'d':4}

I want to get the sum ( base + job__a + users__c)

how can I write the filter statement,

and raw sql is fine too.

Thanks

Jarno Lahtinen · Accepted Answer · 2021-05-25 06:37:27Z

2

You should accomplish this (updated):

    queryset = Priority.objects.annotate(
        a=Coalesce(
            Cast(KeyTextTransform('a', 'job'), output_field=FloatField()),
            Cast(V(0.0), output_field=FloatField())
        ),
        c=Cast(KeyTextTransform('c', 'users'), output_field=FloatField()),
    ).annotate(
        sum=Sum(
            F('base') + F('a') + F('c'), output_field=FloatField() 
        )
    )

    for item in queryset:
        print(item.sum)

edited May 25, 2021 at 6:37

answered May 24, 2021 at 12:28

Jarno Lahtinen

1,71318 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newlife Over a year ago

Thanks ,if the key(a) is not in the json Field(job), and I want to give a default value (0.0.),how to handle this ?

Jarno Lahtinen Over a year ago

You can use F('a')

Collectives™ on Stack Overflow

in django ,how to sum a int field and two float field in jsonb using postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related