On client project I have to do a search in a structure of tables and return
results including some child objects. A classical instance of the N+1 query
problem, if I was using an ORM. I decided not to use the ORM for the feature
because it will be one of the hottest paths of the application and I wanted
more control over the queries (rather complex logic with LIKE & sorting).
But the filtering/sorting is not the topic today, so I will leave it out in
For illustration, let’s assume the following schema:
Without any further thinking I would do a query like this and a bit of code
that constructs parent objects with their respective children.
Just before I started writing the logic to pull apart the result and put it
into it’s object structure, I thought, “It would be nice to let the database
put together all the children of one parent into an array.”
I already knew
which aggregates all values into an array. After some reading I discovered
which takes a sequence of keys and values and creates a JSON object from it.
So my final query looked like this:
When the query is executed with sqlalchemy, children in the resulting rows
is already correctly typed as a list of dictionaries.
The explain analyze output for both queries (on a trivially small test set)
shows that the aggregated version is 50-100% slower (150-200μs vs. 250-350μs),
but I guess that will rarely be a real problem because - at least in my case
the execution time of my query is dominated by filtering/sorting the parent