I need some help with this query. This is what I have so far:
SELECT p.*, to_json(u) AS creator, json_agg(c) AS comments, json_agg(co) AS commenter FROM posts p INNER JOIN users u ON u.id="userId" INNER JOIN comments c ON p.id="postId" INNER JOIN users co ON co.id=c."createdBy" WHERE p.id = ? GROUP BY p.id, u.id, co.id;
Sample Data
POST table
"id": "5f82e0f7-e294-4b49-8f4c-7d408cd81925", "body": "Post text", "media": ["image", "video", "image"], "userId": "64dfa0c1-3756-4560-9103-a1b3c4c7112b", "createdAt": "2020-12-21T17:20:10.929Z", "updated_at": "2020-12-21T17:20:10.929Z",
USERS table
"id": "64dfa0c1-3756-4560-9103-a1b3c4c7112b", "handle": "@bartsimpson", "profilePicUrl": "https://pngimg.com/uploads/simpsons/simpsons_PNG12.png", "createdAt": "2020-12-21T17:20:10.929085", "updated_at": "2020-12-21T17:20:10.929085", "name": "Bart Simpson", "email": "bart@gmail.com",
COMMENTS table
"id": "cd40a98e-3d57-4d86-8f87-662492be7043", "body": "Nice picture Bro", "createdBy": "f58c7eda-3718-41ed-add8-1d13ef8c9268", "postId": "5f82e0f7-e294-4b49-8f4c-7d408cd81925", "createdAt": "2020-12-21T17:20:10.929085", "updated_at": "2020-12-21T17:20:10.929085"
Desired Output
"post": { "id": "5f82e0f7-e294-4b49-8f4c-7d408cd81925", "body": "Milhouse and I are about to go live. In this episode we are going to revisit a classic, we are going to prank Moe! Subscribe from just $2", "likes": 22000, "media": [ "https://static3.cbrimages.com/wordpress/wp-content/uploads/2020/09/simpsons-moe.jpg?q=50&fit=crop&w=960&h=500&dpr=1.5" ], "userId": "64dfa0c1-3756-4560-9103-a1b3c4c7112b", "createdAt": "2020-12-21T17:20:10.929Z", "updated_at": "2020-12-21T17:20:10.929Z", "creator": { "id": "64dfa0c1-3756-4560-9103-a1b3c4c7112b", "handle": "@bartsimpson", "profilePicUrl": "https://pngimg.com/uploads/simpsons/simpsons_PNG12.png", "createdAt": "2020-12-21T17:20:10.929085", "updated_at": "2020-12-21T17:20:10.929085", "name": "Bart Simpson", "email": "bart@gmail.com", "hash": "$2y$10$lFk5u4CR5yDmOjO3gYrC9elFoKuJgD7dY6rcveEBmWZD6.SSl6Vna" }, "comments": [ { "id": "cd40a98e-3d57-4d86-8f87-662492be7043", "body": "Nice picture Bro", "createdBy": "f58c7eda-3718-41ed-add8-1d13ef8c9268", "postId": "5f82e0f7-e294-4b49-8f4c-7d408cd81925", "createdAt": "2020-12-21T17:20:10.929085", "updated_at": "2020-12-21T17:20:10.929085", "commenter": { "id": "f58c7eda-3718-41ed-add8-1d13ef8c9268", "handle": "@homerjsimpson", "name": "Homer Simpson", "email": "homer@gmail.com", "profilePicUrl": "pic_url", "createdAt": "2020-12-21T17:20:10.929085", "updated_at": "2020-12-21T17:20:10.929085", } } ], }
Basically, I want to add the commenter
to each of the comments
but I don't know how to extend my current query to do that.
Have I taken a wrong turn somewhere? I would appreciate any advice.
Thanks
https://stackoverflow.com/questions/65416304/sql-attach-an-object-to-each-element-of-json-array December 23, 2020 at 05:55AM
没有评论:
发表评论