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.
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);
})