4 ms·
With Postgres: SELECT u.name, json_agg(p) AS posts FROM users u, posts p WHERE p.user_id = u.user_id GROUP BY u.user_id;
by exogen 12y ago
With Postgres:
SELECT u.name, json_agg(p) AS posts
FROM users u, posts p
WHERE p.user_id = u.user_id
GROUP BY u.user_id;
- mintplant 12y agoThank you so much for this! I spent a good chunk of today trying to work out how to do this, and had given up concluding that it wasn't possible.
- exogen 12y agoGlad it helped! :) It honestly took me a while to boil it down to that – the docs for those JSON functions are poorly explained and illustrated, IMO.
- mjrpes 12y agoI was playing around with postgres and was able to get a query that puts everything in JSON: select json_agg(sub) from (select u.username, (select array_agg(p) from posts p where u.id = p.user_id) posts from users u) sub;
- danneu 12y agoUsing json_build_object (Postgres 9.4) to map each username to an array of posts: SELECT json_build_object( u.username, (SELECT json_agg(p) FROM posts p WHERE u.id = p.user_id) ) FROM users u Output: [ {"chuck": null}, {"blair": [ {"id": 1, "markup": "hello"}, {"id": 4, "markup": "world"} ]}, {"serena": [{"id": 5, "markup": "testing"}]} ] At least I think you were trying to do that.
- deleted 12y ago[deleted]
- mjrpes 12y agoEDIT: Nevermind my last post. Your query makes sense now and would indeed work well.
- danneu 12y agoWhat if you only want the id and markup of each post? Postgres 9.4 gave us json_build_object: SELECT u.name, array_agg( json_build_object( 'id', p.id, 'markup', p.markup ) ) posts FROM users u, posts p WHERE p.user_id = u.user_id GROUP BY u.user_id;