3 ms·
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
by johannes1234321 1y ago
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 1y agoThe world deserves something better than json_agg. For XTDB we coined NEST_ONE and NEST_MANY: https://xtdb.com/blog/the-missing-sql-subqueries 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 1y agoThere'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 1y agoThere 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)