AVG Decimal simplejson (Decimal is not JSON serializable)

If you use AVG aggregation function on Integer field in MySQL,

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'avg_rate': 'SELECT AVG(hotel_review.rate) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

you will have in a result { “avg_rate” : Decimal(’2.6667′)} and an “Exception Value: Decimal(’2.6667′) is not JSON serializable”.
In this case you can do this:

from django.utils import simplejson
from decimal import Decimal

class MyJSONEncoder(simplejson.JSONEncoder):
    """JSON encoder which understands decimals."""

    def default(self, obj):
        '''Convert object to JSON encodable type.'''
        if isinstance(obj, Decimal):
            return "%d" % obj
        return simplejson.JSONEncoder.default(self, obj)

hotels_json = simplejson.dumps(list(hotels.values()), cls = MyJSONEncoder)

You can read about extra search fields and JSON serialization in our article

Extra Select field in JSON

Fields from extra select query

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'review_count': 'SELECT COUNT(*) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

could be used in templates:

{% for hotel in hotels %}
    {{hotel.name}} has {{hotel.review_count}} reviews
{% endfor %}

But when you try to serialize hotels QuerySet, you will have only fields from model and you won’t be able to access extra select field named “review_count” in resulting JSON file.

The solution is to serialize hotels.value() object:

from django.utils import simplejson

hotels_json = simplejson.dumps(list(hotels.values()))

And your JSON will include “review_count” field. It will look like this:

[
   {
      "name":"Hilton",
      "city":"Paris",
      "review_count":3
   }
]

Filter annotate() COUNT()

If you use annotate() function in your Django query and want to apply filters for it, you won’t be able to do it.
This is where extra() function can be used.

For example, here is my hotel app:

from django.db import models
class Hotel(models.Model):
    name = models.CharField(max_length=100)
    city = models.CharField(max_length=100)

class Review(models.Model):
    is_active = models.BooleanField(default =  False)
    content = models.CharField(max_length=500)
    hotel = models.ForeignKey(Hotel)

If I make search for hotels, that are situated in Paris and in my result view I want to display number of reviews for each object. I do this way:

from django.db.models import Count
hotels = Hotel.objects.filter(city = "Paris").annotate(review_count = Count(review))

But if I want to count only those reviews, that are active, I can not add one more filter() function, because conditions in all filter() function are combined together in WHERE statement of resulting SQL query, but I do not want to filter Hotel objects by hotel_review.is_active = True condition.

To tackle this we can do the following:

hotels = Hotel.objects.filter(city = "Paris").extra(
    select={
        'review_count': 'SELECT COUNT(*) FROM hotel_review WHERE hotel_review.is_active = 1 AND hotel_review.hotel_id = hotel_hotel.id',
    },
)

How to use review_count parameter in template and JSON objects see article about Extra Select Field in JSON