Is it possible to combine the outcomes of a SQL query that produces numerous entities with identical identifiers?

Currently working on my first Node.js API project. Within the '/posts' endpoint, I am receiving data in this format:

[
    {
        "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"
    }
]

I am looking to merge objects with the same POST_ID and create an array of URLs for each post under the key 'postImages'. Here's an example of the desired output:

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

The SQL Query being used 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).

javascriptexpress

Answer №1

While I don't have access to a SQL SERVER to directly test this code, it appears that the output you are looking for could potentially be generated using the query provided below.

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

The result of this query should provide a JSON representation of the desired POST_PHOTO_URLs. To convert these into an array format, parsing will be necessary.

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

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Sequencing numerous promises (managing callbacks)

I am encountering some challenges with promises when it comes to chaining multiple ones. I'm having difficulty distinguishing how to effectively utilize promises and their differences with callbacks. I've noticed that sometimes callbacks are trig ...

javascriptangularjspromiseangular-promise

Establishing the null values for input parameters

Whenever the button is clicked, I want to reset the input values to null. The function spremi() gets triggered when the button is clicked, but I want to set the values of podaci.tezina and podaci.mamac back to their initial values so that the user can en ...

javascriptangularjsinputionic-framework

Get every possible combination of a specified length without any repeated elements

Here is the input I am working with: interface Option{ name:string travelMode:string } const options:Option[] = [ { name:"john", travelMode:"bus" }, { name:"john", travelMode:"car" }, { name:"kevin", travelMode:"bus" ...

javascripttypescriptalgorithmecmascript-6combinations

Manipulating values within a multidimensional array using JavaScript

I am attempting to populate a multidimensional array with data from a JSON file. The issue I am facing is that I am unable to update the content in the array: if(j>i && hoursYy == hoursY && secondsX == secondsXx){ wocheArray[i].count = wocheArray[i] ...

javascriptarraysjsonmultidimensional-array

Ajax is not able to trigger a POST request to a PHP form

My form is not posting for some unknown reason, despite everything else on my server functioning properly. Below is the code snippet in question: PHP <?php if(isset($_POST['field1']) && isset($_POST['field2'])) { $data ...

javascriptphpjqueryajaxforms

Transferring Data from Angular Application to Spring Server via Rest Implementation

I am currently facing an issue while attempting to upload a file using Angular and send it to my Spring-based REST backend. I followed the guidance provided in this resource for my Angular service implementation. However, two problems have arisen: The fir ...

javascriptjavaspringrestangular

What makes fastify-plugin better than simply calling a regular function?

I recently came across a detailed explanation of how fastify-plugin operates and its functionality. Despite understanding the concept, I am left with a lingering question; what sets it apart from a standard function call without using the .register() metho ...

javascripttypescriptfastify

Open in a new tab for enhanced content formatting in Prismic on NextJs

In my Prismic RichText editor, I have included two files (terms and conditions) that I would like to open in a new tab. Unfortunately, Prismic does not offer an option like target _blank for this functionality. I am currently working with NextJs and Tail ...

javascriptnext.jstailwind-cssprismic.iotailwind-in-js

What steps should be taken to resolve the update problem in Sequelize?

Having an issue with the update method in MySQL ORM. User.update({ ResetPasswordToken : resetPasswordToken },{ where: { UserName: 'testuser' } }) Receive this Sequelize Log: Running query: UPDATE Users SET ResetPasswordToken=?,updat ...

javascriptmysqlnode.jsexpresssequelize.js

Troubleshooting: Issues with AngularJS $route.reload() functionality

I have an angular app and I'm attempting to refresh the page. I've tried using $route.reload() as recommended in multiple posts, but I can't seem to get it to work (Chrome is showing me an error). Here's my controller: var app = angula ...

javascriptangularjstwitter-bootstrap

"Angular: Enhancing Functionality with Nested Controllers and Service Dependency Handling

Hey there! I've got a setup with nested angular controllers. The outer one is called personController, while the inner one is personScheduleController. In this arrangement, the person controller reaches out to a service to retrieve person data. On the ...

javascriptangularjs

Words of wisdom shared on social media

How can I share text from my HTML page on Twitter? This is the code snippet from my HTML page - function change() { quotes = ["Naam toh suna hi hoga", "Mogambo Khush Hua", "Kitne aadmi the?"]; auth = ["Raj", "Mogambo", "Gabbar"]; min = 0; max = ...

javascriptjqueryhtmltwitter

Exploring the depths of Cordova's capabilities: implementing a striking 3D front-to-back screen flip effect

Recently, I came across an interesting app that had a unique feature. By clicking on a button, the entire screen would flip from front to back with a 3D effect on iPhone. It was quite impressive to see in action. The developer mentioned that he achieved t ...

javascriptjqueryioscsscordova

Mongoose: The remove() function will confirm as true even for items that have already been

My current code snippet always displays "User deleted" even after the user has been removed. Ideally, I would like to return a 404 error in this scenario without making excessive database queries. Is there a way to retrieve the status of userNotFound with ...

node.jsmongodbexpressmongoosedatabase

The clear icon in React will only appear when there is a value inputted

Is there a way to implement the clear X icon that only appears when typing in the input field using reactjs, similar to the search input on the Google homepage? Check it out here: https://www.google.com import { XIcon } from '@heroicons/react/solid&ap ...

javascriptreactjs

External JavaScript files cannot be used with Angular 2

I am attempting to integrate the twitter-bootstrap-wizard JavaScript library into my Angular 2 project, but I keep encountering the following error: WEBPACK_MODULE_1_jquery(...).bootstrapWizard is not a function I have created a new Angular app using a ...

javascriptangulartypescript

VueJS - Validating Props with Objects

What is the best way to validate Object type props in VueJS to guarantee that certain fields are defined within the object? For instance, I need to make sure that the user prop includes 'name', 'birthDate', and other specific fields. ...

javascriptvue.jscomponents

Javascript navigation menu failing to accurately display all pages

As I continue to enhance my website at , I have encountered an issue with the menu functionality. The menu is dynamically generated through JavaScript, scanning a folder for pages and populating them into an array. While this system functions smoothly ove ...

javascriptphpdebuggingmenuslideshow

Manipulate a property within an array using JavaScript and AngularJS

I am working with an AngularJS model that looks like this: $scope.Model = { Users : [{ UserId: '', FirstName: '', LastName: '' }], Products :[{ ProductId: '', ...

javascriptarraysangularjs

Develop a distinctive JavaScript solution that enables an image to appear in fullscreen upon loading the page, and then smoothly fade away after precisely

I have been trying to incorporate a feature on my Shopify frontage website where our logo appears fullscreen and then fades away or disappears after a few seconds, allowing people to access the rest of the website. However, I am facing difficulty in making ...

javascripthtmlcssanimationshopify