| 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. |
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 ...