django - aggregate json field specific keys and or

问题:

I have a model with the a field data of type JSONField from django.contrib.postgres.fields. The json structure is like so:

{'aa': 1, 'bb': 2, 'cc': 4}

I want to aggregate the sums of the aa and cc keys - so in this case, it will be 5. Also - i cannot promise that either aa or cc will be in the json.
Is this possible? If so - i want to order by the aggregated data.
Example:

1. id: 1, data = {'aa': 1, 'bb': 2, 'cc':4}
2. id: 2, data = {'aa': 3, 'bb': 2}
3. id: 3, data = {'cc': 7}
4. id: 4, data = {'bb': 7}

I want to do a query, something like:
MyModel.objects.aggregate(my_sum).order_by(my_sum)
After aggregation the ordered rows in the queryset will be:

1. id: 3
2. id: 1
3. id: 2
4. id: 4

Thanks!

回答1:

I understood that you want to sum up a value and b value for each row and then order each row by sum value. right?

-> ->> This is how to select key or value in JSON format in PostgreSQL(I don't know if it also works in MySQL or others, I normally worked with PostgreSQL). There is good resource in here. your data in a column named 'data' is {"aa":3, "bb":2, "cc":5}. so you select aa value by data->>'aa'. What if {'classification':{'pc':5000}}? you need to select pc value. Then data->'classification'->>'pc'

:: notation is cast operation.

CAST(data->'aa' AS INTEGER)

data->'aa'::int

class RawSQL(sql, params, output_field=None)

RawSQL("((data->>'aa'::int), (0,)") doesn't mean that if aa doesn't exist, it has 0 value. 0 is params.

queryset.annotate(val=RawSQL("select col from sometable where othercol = %s", (someparam,)))

Well, if you can modify your data like this

• id: 1, data = {'aa': 1, 'bb': 2, 'cc':4}
• id: 2, data = {'aa': 3, 'bb': 2, 'cc':0}
• id: 3, data = {'cc': 7, 'bb': 0, 'cc':0}
• id: 4, data = {'bb': 7, 'bb': 0, 'cc':0}

This can work.

Contract.objects.annotate(
sumVal=RawSQL("((data->>'aa')::int)", (0,))+RawSQL("((data->>'cc')::int)",(0,)))
.order_by('sumVal')

---

I suggested using Coalesce. the author of this question figured out. There is code below.

raw_sql = "+".join(["COALESCE((data->>%s)::int, 0)" for _ in ['aa', 'cc']) 
MyMoodel.objects.all()
.annotate(my_sum=RawSQL(raw_sql, params=('aa', 'cc')))
.order_by('my_sum')

回答2:

 YourModel.objects.annotate(aa=RawSQL("((data->>'aa')::int)", (0,)),
                            cc=RawSQL("((data->>'cc')::int)", (0,))) \
                .aggregate(total=Sum('aa')+Sum('cc')).order_by('total')