Bulva Bulva - 1 year ago 53
JSON Question

How serialize count() from sql query to JSON

I have spatial query for postgresql (and postgis). I use raw() function. So I have something like this:

observations = Square.objects.raw('SELECT validation_birds_square.id AS id,
validation_birds_square.identification,
Count(validation_birds_checklist.position) AS total
FROM validation_birds_square ...the rest of sql query...')


I use Square model which I defined in my models.py. Then I want serialize observations, so I make this:

return HttpResponse(serializers.serialize("json", observations), content_type='application/json')


But here is the problem. It serialize only properties of Square model, not total from sql query.

When I iterate over observations I can get that value:

for observation in observations:
print(observation.total)


Is there any better way how to put total to serialized JSON than iterate over observations and serialize it manually to JSON?

Answer Source

You don't have a proper queryset, so there isn't much point in using the built-in serializers. Just create a list of dicts, and use JsonResponse to return it as JSON.

data = [{'id': o.id, 'identification': o.identification, 'total': o.total} for o in observations]
return JsonResponse(data)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download