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