Aggregate deeply nested JSONField data in Django

Written by on October 24, 2018, 6:19 am

I ran across this problem the other day and it seems there aren't useful answers on this particular issue.

A combination of Django and Postgres makes it very easy to persist JSON data in your models. I think generally when building an application data schema storing JSON data is usually a bad idea. In fact this post and its partial solution is an example of issues you can have expecting JSON fields to behave predictably (in ORM terms).

In this case I wanted to store responses from a third party API and didn't think I would need to query data in those responses (hence no formal schema for the values within). Of course a requirement eventuated that required querying and then aggregation on values in the JSON I was storing and that is where I hit the problem described below.

(N.B. As I write this, with some hindsight, I think the correct solution is actually to migrate the values out of the JSON and store them in dedictated fields in the model, but anyway).

So if you want to filter by deeply nested values in a JSONField that part is fine. It actually works nicely with query_set syntax that matches doing joins across reverse relationships or foreign keys.

class ThingResponse(TimeStampedModel):
user = models.ForeignKey(User, on_delete=models.PROTECT, related_name="thing_responses")
    data = JSONField(_('The actual response from thing'))

If an example data looks like:

  "house": {
    "name": "Lannister",
    "seat": "Casterly Rock",
    "scion": "Tywin",
    "heir": "Jaime",
    "chapter_introduced": 5
  "name" : "Tyrion Lannister",
  "age" : 24

and you want to find all the Lannisters, you could write a queryset like this:


Things get tricky when you want to aggregate data in the JSONField. Aggregating on a value at the top level is fine (as described in this SO answer):

from django.db.models.functions import Cast
from django.db.models import Min, IntegerField
from django.contrib.postgres.fields.jsonb import KeyTextTransform

ThingResponse.objects.annotate(age_value=Cast(KeyTextTransform('age', 'data'), IntegerField())).aggregate(Min('age_value'))

You can annotate the queryset with nested values by nesting the KeyTextTransform like this:

ThingResponse.objects.annotate(chapter=Cast(KeyTextTransform('chapter_introduced', KeyTextTransform('house', 'data')), IntegerField()))

So this works fine and will annotate your result with a 'chapter' property with the nested value in it. Problem happens when you try and aggregate on that nested value:

ThingResponse.objects.annotate(chapter=Cast(KeyTextTransform('chapter_introduced', KeyTextTransform('house', 'data')), IntegerField())).aggregate(Min('chapter_introduced'))

You'll get an error like this:

File "[env path]/lib/python3.6/site-packages/django/db/models/sql/", line 128, in get_group_by if (sql, tuple(params)) not in seen: TypeError: unhashable type: 'list'

I think what is happening here is the KeyTextTransform hasn't been resolved by this time so instead of a value it is getting a list of arguments.

So this is as far as I got with this and ended up implementing the aggregation in memory. If you do have a similar issue and find a solution to the issue with aggregating on nested JSON values I would love to hear from you.

Permalink - Comments - Tags: Development

Writing Great Fiction - Lecture Three Exercise

Written by on September 17, 2018, 12:32 pm

The writing for this lecture was to take the styles in the following three novels and apply them to a passage from the others:

  • - The Great Gatsby - First person account.
  • - Mrs Dalloway - Third person stream of consciousness.
  • - The Maltese Falcon - Third person, literal observation.
My first attempt was to re-write this Great Gatsby scene using something like the third person stream of consciousness from Mrs Dalloway.

Original Scene

“Now, don’t think my opinion on these matters is final,” he seemed to say, “just because I’m stronger and more of a man than you are.” We were in the same senior society, and while we were never intimate I always had the impression that he approved of me and wanted me to like him with some harsh, defiant wistfulness of his own.

We talked for a few minutes on the sunny porch.

“I’ve got a nice place here,” he said, his eyes flashing about restlessly.

Turning me around by one arm, he moved a broad flat hand along the front vista, including in its sweep a sunken Italian garden, a half acre of deep, pungent roses, and a snub-nosed motor-boat that bumped the tide offshore.

“It belonged to Demaine, the oil man.” He turned me around again, politely and abruptly. “We’ll go inside.”

My Version

“I’ve got a nice place here,” Tom said as his eyes enumerated each feature of garden, lawn and shore, pleased how the magnificent view was presented in the afternoon glow. He liked to impress his guests, but wondered at his eagerness for the admiration of this particular man. Nick was unimpressive in almost every way, at least in Tom's estimation, and yet he found he valued his company, perhaps even his friendship?

Nick watched the restless display with some comprehension and allowed himself to be guided, not impolitely, though certainly forcefully, through a conversation on the virtues of Tom's domain.

Tom, satisfied he had established his preemminance, abruptly turned and guided the two of them inside.

"It belonged to Demaine, the oil man." Tom said before passing into the high ceilinged hallway. Nick assumed he should know who that was and nodded.

My second attempt was taking the third person stream of consciousness of Mrs Dalloway and attempting the impersonal third person literal style from the Maltese Falcon:

Original Scene

She would have been, in the first place, dark like Lady Bexborough, with a skin of crumpled leather and beautiful eyes. She would have been, like Lady Bexborough, slow and stately; rather large; interested in politics like a man; with a country house; very dignified, very sincere. Instead of which she had a narrow pea-stick figure; a ridiculous little face, beaked like a bird's. That she held herself well was true; and had nice hands and feet; and dressed well, considering that she spent little. But often now this body she wore (she stopped to look at a Dutch picture), this body, with all its capacities, seemed nothing--nothing at all. She had the oddest sense of being herself invisible; unseen; unknown; there being no more marrying, no more having of children now, but only this astonishing and rather solemn progress with the rest of them, up Bond Street, this being Mrs. Dalloway; not even Clarissa any more; this being Mrs. Richard Dalloway.

Bond Street fascinated her; Bond Street early in the morning in the season; its flags flying; its shops; no splash; no glitter; one roll of tweed in the shop where her father had bought his suits for fifty years; a few pearls; salmon on an iceblock.

"That is all," she said, looking at the fishmonger's. "That is all," she repeated, pausing for a moment at the window of a glove shop where, before the War, you could buy almost perfect gloves. And her old Uncle William used to say a lady is known by her shoes and her gloves. He had turned on his bed one morning in the middle of the War. He had said, "I have had enough." Gloves and shoes; she had a passion for gloves; but her own daughter, her Elizabeth, cared not a straw for either of them.

Not a straw, she thought, going on up Bond Street to a shop where they kept flowers for her when she gave a party. Elizabeth really cared for her dog most of all.

My Version

A thin, dignified woman moved from shopfront to shopfront along Bond Street. The grandure of the flags that adorned each was not reflected in the shops themselves. The lady, for that is clearly what she was, upright and deliberate as she moved up the street, considered a roll of tailor's tweed as she passed. Despite her careful manner she was clearly enjoying herself, her pointed face moving quickly to take in each modest display.

Glancing from the fishmongers, with its forlone salmon presented on a block of ice, towards a glove shop. She paused and looked, slightly wistful, on the pitiful spread of gloves, much diminished after four years of war. Her composure seemed to slip and she muttered to herself 'That is all ... that is all', barely audible over the noise of the street.

Permalink - Comments - Tags: Stories

Writing Great Fiction - Lecture Two Exercise

Written by on September 9, 2018, 9:38 am

Lecture two from Writing Great Fiction: Storytelling Tips and Techniques is about evocation.

Based on an exercise from John Gardner's The Art of Fiction we are supposed to write a passage describing a building, a landscape or an object from the point of view of a parent who's child has just died. All you're allowed to do is describe the object without mentioning the child, the parent or death. Invoke the feeling of loss and grief without mentioning either.

After a couple of abortive tries where I eventually mentioned one or all, I came up with this:

The swingset was tiny. Much too small for an adult. The worn rubber seat had two leg holes and a pink plastic strap across the front. The purple metal frame was faded and chipped, but you could still make out capering, grinning figures along its length. Under the seat was a muddy puddle, the center of a trail of scuff marks. Footprints staggered and skipped at the edge of the puddle and into the dust beyond. A gust of wind rippled the water and the hinges squeaked as the rubber harness twisted slowly. The wind died and there was silence.

Exercise from Lecture Three >>

Permalink - Comments - Tags: Stories

Writing Great Fiction - Lecture One Exercise

Written by on September 5, 2018, 9:30 am

I am listinging to the Audible series Writing Great Fiction: Storytelling Tips and Techniques. I'll be posting my attempts at the writing exercises at the end of each lecture here. Following is my attempt at writing a passage based on a single image that had struck me in real life.

Fisher sat, stooped on the bench outside the cafe. Not an old man, but grey nevertheless, weathered and tired. His faded woolen hat was pulled down over his brow, almost covering his eyes. Sometimes he imagined if he couldn't see people, they couldn't see him.

The birds shuffled forward as another shower of breadcrumbs fell around his feet. He smiled grimly. He didn't really like the birds, but he liked feeling in control. When one brave creature flapped onto his knee he scowled in shock and swept it to the ground. The bird struggled to its feet and returned, pecking and shoving, into the scrum. He stared at the pigeon and wondered.

A car horn burped in the distance and he stood abruptly, hurrying to the nearby stairs leading up to street level. He was late and the scowl returned to his face. His employer only seemed to acknowledge his presence to rebuke Fisher for some minor, though admittedly frequent, infraction. His tardiness would certainly be noticed.

Exercise from Lecture Two >>

Permalink - Comments - Tags: Stories

Aggregate functions are not allowed in this query

Written by on September 22, 2017, 11:19 am

Got burnt today by an odd error thrown by the Django ORM when writing some tests. It was an easy mistake to make and the exception "Aggregate functions are not allowed in this query" has lots of unrelated Google juice and was not helpful in getting to the bottom of the problem. Hence this post.

Start with a pretty simple model that we are going to be updating via an API.

class Foo(models.Model):
    name = models.CharField(max_length=512)
    value = models.CharField(max_length=512)

Then write some code to hit an API via requests and update the database with a new value.

import requests
from .models import Foo

def make_request():

    # go get foo from your api
    resp = requests.get("http://your.thing/api/foo/name")

    # the name and value of the foo?
    value = resp.json()["value"]

    # Grab the instance of foo corresponding to name
    # and update its value
    foo = Foo.objects.get(name="name")
    foo.value = value

Then a test using a patch on yourthing.helpers.requests so we don't hit the real API when we run our test. Let's pretend that make_requets has some side effect that we are testing for rather than the value stored in the Foo table. Given that assumption we might not mock the actual values returned by the API (see THIS --->).

from mock import Mock, patch

from django.test import TestCase

from .models import Foo
from .helpers import make_request

class MockTestCase(TestCase):

    def test_an_api(self, mock_requests):

        # Create the instance we are going to update
        Foo.objects.create(name="name", value=0)

        # THIS ---> mock_requests.get.return_value.json.return_value = { "value" : 1}

        # Test for something unrelated to the contents of the Foo table. Perhaps some other
        # side effect of the make_requests() call.

Run this test and you will get this error.

ERROR: test_an_api (yourthing.test_helpers.MockTestCase)
Traceback (most recent call last):
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/mock/", line 1305, in patched
return func(*args, **keywargs)
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/", line 18, in test_an_api
File "/Users/tomhorn/dev/sandbox/test_mock/yourthing/", line 17, in make_request
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/", line 807, in save
force_update=force_update, update_fields=update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/", line 837, in save_base
updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/", line 904, in _save_table
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/", line 954, in _do_update
return filtered._update(values) > 0
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/", line 664, in _update
return query.get_compiler(self.db).execute_sql(CURSOR)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/", line 1191, in execute_sql
cursor = super(SQLUpdateCompiler, self).execute_sql(result_type)
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/", line 863, in execute_sql
sql, params = self.as_sql()
File "/Users/tomhorn/environments/test_mock/lib/python3.6/site-packages/django/db/models/sql/", line 1143, in as_sql
raise FieldError("Aggregate functions are not allowed in this query")
django.core.exceptions.FieldError: Aggregate functions are not allowed in this query

This unhelpful error might lead us to examine queries & relationship looking for an aggregate function. In fact there is no aggregate function. Instead this is happening because without an explict mock the resp.json()["value"] call in make_request will return a MagicMock instead of a string. When the ORM tries to save the instance it gets into trouble dealing with a field that will mock any property it tries to access.

<MagicMock name='requests.get().json().__getitem__()' id='4385055128'>

So I guess the moral of the story is to make sure you mock every response that ends up being passed to a Django ORM model instance. If you don't you might spent a chunk of time investigating rabbit holes as I did.

Permalink - Comments - Tags: Development,Django