4 ms·
Using json_build_object (Postgres 9.4) to map each username to an array of posts: SELECT json_build_object( u.username, (SELECT json
by danneu 12y ago
Using 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.