4 ms·
Thank 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.
by mintplant 12y ago
Thank 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.