[johannes1234321]

It follows the relational algebra model. Relations (aka Tables) go in, relations (aka Tables) come out. This makes some things really nice.

However I proposed a hierarchical result for such cases a long time to our database, but couldn't convince enough people. json_agg came later at there all the machinery is there, it would "just" require exposing this to the protocol and adapting all clients to understand that data format ...

[refset]

The world deserves something better than json_agg. For XTDB we coined NEST_ONE and NEST_MANY: https://xtdb.com/blog/the-missing-sql-subqueries

The output format is either raw Arrow DenseUnions (e.g. via FlightSQL) or Transit via a pgwire protocol extension type.

[andyferris]

There's nothing in the relational model that suggests a field (cell) can't take the value of a relation. Only SQL makes that difficult.

[johannes1234321]

There is nothing forbidding it, but then you can't process it further with the same algebra, that value then is a single opaque value. (Which for many uses is fine as that should be one of the final steps of processing)