This type commonly appears when one does 1-M joins with aggregation. For example, if something has a set of associated tags, you can retrieve multiple somethings AND all of their tags in one go:
SELECT something.id, something.foo, array_agg((tag.label, tag.attributes))
FROM something
NATURAL JOIN tag
GROUP BY something.id
In many cases the same could be accomplished by using json_agg, but that would fail with binary or otherwise complex data.
I've split commits as best as I could, so please see commit messages for more details regarding every change I made here.
jackc
commented
2 years ago
This type commonly appears when one does 1-M joins with aggregation. For example, if something has a set of associated tags, you can retrieve multiple somethings AND all of their tags in one go:
In many cases the same could be accomplished by using
json_agg, but that would fail with binary or otherwise complex data.I've split commits as best as I could, so please see commit messages for more details regarding every change I made here.