SQL - Attach an object to each element of json array

Question

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": "[email protected]",

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": "[email protected]",
      "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": "[email protected]",
            "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

@eshirvana Added sample data and desired output. Thanks for the help! — mcpolandc
– mcpolandc, Commented Dec 22, 2020 at 22:23

GMB · Accepted Answer · 2020-12-23 01:25:55Z

1

Here is one way to "inject" the commenter in each comment:

select p.*,
    to_json(u) as creator,
    jsonb_agg(
        to_jsonb(c) 
        || jsonb_build_object('commenter', to_jsonb(co))
    ) as comments
from posts p
inner join users u on u.id = p.userid
inner join comments c on p.id= p.postid
inner join users co on co.id = c.created_by
where p.id = ?
group by p.id, u.id

This uses JSONB concatenation operator || to insert a new key in each comment array, that embeds the maps the commenter record. If you are using json instead of jsonb, you need to cast the values.

edited Dec 23, 2020 at 1:25

answered Dec 23, 2020 at 1:06

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL - Attach an object to each element of json array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related