How can I merge results from a SQL query that returns multiple objects with the same id?-CodePudding

I'm building my first Node.js API. In the endpoint '/posts' I have a return like this:

[
    {
        "POST_ID": 1,
        "POST_TITLE": "Post N.1",
        "POST_DESCRIPTION": "Description for Post N.1",
        "POST_PHOTO_URL": "Url for image 1 of post 1"
    },
    {
        "POST_ID": 1,
        "POST_TITLE": "Post N.1",
        "POST_DESCRIPTION": "Description for Post N.1",
        "POST_PHOTO_URL": "Url for image 2 of post 1"
    },
    {
        "POST_ID": 2,
        "POST_TITLE": "Post N.2",
        "POST_DESCRIPTION": "Description for Post N.2",
        "POST_PHOTO_URL": "Url for image 1 of post 2"
    },
    {
        "POST_ID": 2,
        "POST_TITLE": "Post N.2",
        "POST_DESCRIPTION": "Description for Post N.2",
        "POST_PHOTO_URL": "Url for image 2 of post 2"
    }
]

How can I merge the objects that have the same POST_ID, and make the POST_PHOTO_URL an array that contains all the URLS for the same post?

I want something like this:

responseObj = {
    postId: 0,
    postTitle: "Post N.1",
    postDescription: "Description for Post N.1",
    postImages: ['first_url', 'second_url'],
  };

My SQL Query is: SELECT P.POST_ID, P.POST_TITLE, P.POST_DESCRIPTION, PI.POST_PHOTO_URL FROM POST P INNER JOIN POST_ITEMS AS PI ON P.POST_ID = PI.POST_ID SQL SERVER.

CodePudding user response：

I have no SQL SERVER to test this, but your desired output could probably be created with the following query.

SELECT P.POST_ID AS postId, P.POST_TITLE AS postTitle, P.POST_DESCRIPTION AS postDescription, oa.postImages
FROM POST P
OUTER APPLY(
    SELECT PI.POST_PHOTO_URL AS postImages
    FROM POST_ITEMS PI
    WHERE PI.POST_ID = P.POST_ID
    FOR JSON PATH
) oa

This should return an JSON representation of the desired POST_PHOTO_URLs. You have to parse these to get your array.

result.forEach(r => {
  r.postImages = JSON.parse(r.postImages);
})