5 ms·
I think relational databases are great, but this is my biggest problem with SQL, even before the ridiculous syntax. A query like select users.*, orders.*
by phiresky 1y ago
I think relational databases are great, but this is my biggest problem with SQL, even before the ridiculous syntax.
A query like
select users.*, orders.* from users left join orders on orders.user_id = users.id
Should always have returned a structure like:
type SingleReturnRow = { users: {id: ..., }[], orders: {user_id: ..., id: ..., }[]}
type Return = SingleReturnRow[]
Mangling the columns together and _removing_ groupings that naturally appear is just so unnecessary.
I don't think a larger change in the query language would even be needed.
Even better of course would be a return value like
type SingleReturnRow = { user: User, orders: Order[] }
But I see how that would require a fundamental change in the language.
Of course in PG now you can use
select users.*, json_agg(orders.*) as orders from users left join orders on orders.user_id = users.id group by users.id
but using JSON as intermediate steps just feels unnatural.
- reaanb2 1y agoSee https://arxiv.org/abs/2312.00638 https://arxiv.org/abs/2312.00638 for one proposal to address this
- johannes1234321 1y agoIt 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)