3 ms·
I 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)
by mjrpes 12y ago
I 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.