SQL - Attach an object to each element of json array

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