[Glyptodon]

I think like 85% of the pain points could be solved by (a) allowing statements to start with a `from` clause, (b) some kind of shorthand for field sets, and (c) having some kind of syntax for returning some things as nested rows/objects. Without thinking deeply at all:

from users u join purchases p on p.user_id = u.id select u{defaults}, u[p{defaults} as purchases] limit 100 [purchases: 10]

returning rows that make `purchases` into a row-nested collection of up to 10 items instead of creating that many more overall rows and repeating the same user info multiple times.

[resharpe105]

You can easily do nested rows/objects in sql.

E.g.in postgres:

SELECT u.*, ( SELECT JSON_AGG(p) FROM ( SELECT p.* FROM purchases p WHERE p.user_id = u.id LIMIT 10 ) p ) AS purchases FROM users u JOIN purchases p ON p.user_id = u.id GROUP BY u.id LIMIT 100;

Starting queries with from - what is the benefit of that beside personal preference?

And you can simply use a cte or a view instead of a field set.

[Glyptodon]

I agree my off the cuff syntax is probably not great, but I do think there's benefit to a more native version of json_agg that doesn't return strings/json, but typed data.

The "start queries with from" is because you get easier and better auto complete and error detection for queries when the tables being used are declared. When you start typing "select some_field" there's basically no way to have good auto complete or correct until the from clause. IMO there are more philosophical reasons too.

Views and CTEs have their place, but also have their shortcomings. That I don't think this solves. But I also don't think they really fill the gap I'm thinking about either.