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