AVG Decimal simplejson (Decimal is not JSON serializable)

Posted: May 26th, 2010 | Author: | Filed under: Django | Tags: | 4 Comments »

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

4 Comments on “AVG Decimal simplejson (Decimal is not JSON serializable)”

  1. 1 Chamal said at 9:59 am on June 25th, 2010:

    Nice simple trick for this Decimal problem I’m facing too ;-)

    thanks, that’s much simplier than recoding an entire json serializer

  2. 2 Ryan said at 8:56 pm on September 30th, 2010:

    This solution truncates the decimal to and int value. It would not give a very good answer unless you actually mean to call floor() on your decimal value.

    A better solution would be to replace line 10:
    return “%d” % obj
    with
    return obj.to_eng_string()

    this way you still get a stringified version of your decimal, without rounding errors or forcing it to an integer.

    Also, since simplejson 2.1.0, you can pass ‘use_decimal=True’ to simplejson.dumps(), which will handle this for you without needing to add this handler.
    django.utils, though, has 2.0.7 as of 1.2 , so this technique is still useful.

  3. 3 Panos Laganakos said at 1:25 pm on October 25th, 2010:

    Thanks mate!

  4. 4 Sam Stoelinga said at 7:02 am on June 14th, 2011:

    For simplejson 2.1 and above the use_decimal parameter is available.

    hotels_json = simplejson.dumps(list(hotels.values()), use_decimal=True)

    This converts the decimal to a float I think.

Leave a Reply