Condense items into objects and arrays when the Express query yields multiple objects in a many-to-many query

Question

Condense items into objects and arrays when the Express query yields multiple objects in a many-to-many query

I have a situation where my SQL queries are returning multiple objects due to a many-to-many mapping in express. I am in search of a tool that can help me simplify these common objects by nesting arrays of objects within them.

SELECT *
FROM User
LEFT JOIN UserAddress ON UserAddress.user_id = User.user_id
LEFT JOIN Address ON Address.address_id = UserAddress.address_id;

When we execute this query, the response looks something like:

[
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 1,
    "address": "1234 address way"
  },
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 2,
    "address": "5678 new address"
  },
  {
    "user_id": 2,
    "name": "diff name",
    "address_id": 1,
    "address": "1234 address way"
  }
]

What I am aiming for is to transform this array using a JavaScript tool so it resembles the following structure:

[
  {
    "user_id": 1,
    "name": "test name",
    "address": [
      {
        "address_id": 1,
        "address": "1234 address way"
      },
      {
        "address_id": 2,
        "address": "5678 new address"
      }
    ]
  },
  {
    "user_id": 2,
    "name": "diff name",
    "address_id": 1,
    "address": "1234 address way"
  }
]

However, I am uncertain about what this transformation process is formally called or if there exists a specific tool that can facilitate this operation?

javascript express

Answer 1

Answer №1

Here is a versatile solution that allows you to specify the data you want to collate and how you want it structured. This function utilizes a Map object to assist in organizing the data based on a specified key (user_id in this case):

function collateData(sourceArray, keyName, collectionName, collectionFields) {
    let collection = new Map();
    for (let item of sourceArray) {
        let targetObj = collection.get(item[keyName]);
        if (!targetObj) {
            targetObj = Object.assign({}, item);
            // Remove the properties being collected
            for (let field of collectionFields) {
                delete targetObj[field];
            }
            targetObj[collectionName] = [];
            collection.set(item[keyName], targetObj);
        }
        
        let collectedObj = {};
        // Copy over the fields being collected
        for (let field of collectionFields) {
            collectedObj[field] = item[field];
        }
        
        targetObj[collectionName].push(collectedObj);
    }
    
    // Convert Map to final array
    return Array.from(collection.values());
}

This approach always stores address fields in an array format for consistency, even when there's only one address (resulting in an array of length 1). This simplifies data handling for users as they can reliably access the address information without complex logic to handle differing formats.

An example usage scenario with sample data:

let data = [
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 1,
    "address": "1234 address way"
  },
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 2,
    "address": "5678 new address"
  },
  {
    "user_id": 2,
    "name": "diff name",
    "address_id": 1,
    "address": "1234 address way"
  }
];

let result = collateData(data, "user_id", "address", ["address", "address_id"]);
console.log(result);

If you prefer not to have single-element arrays, you can perform post-processing to extract individual elements:

// Function remains the same, only slight adjustment at the end for post-processing

let result = collateData(data, "user_id", "address", ["address", "address_id"]);

// Post-processing to remove single-element arrays
for (let item of result) {
    let array = item["address"];
    if (array.length === 1) {
        delete item["address"]; // Remove array
        Object.assign(item, array[0]); // Copy fields back to main object
    }
}

console.log(result);

Answer 2

Here is a versatile solution that allows you to specify the data you want to collate and how you want it structured. This function utilizes a Map object to assist in organizing the data based on a specified key (user_id in this case):

function collateData(sourceArray, keyName, collectionName, collectionFields) {
    let collection = new Map();
    for (let item of sourceArray) {
        let targetObj = collection.get(item[keyName]);
        if (!targetObj) {
            targetObj = Object.assign({}, item);
            // Remove the properties being collected
            for (let field of collectionFields) {
                delete targetObj[field];
            }
            targetObj[collectionName] = [];
            collection.set(item[keyName], targetObj);
        }
        
        let collectedObj = {};
        // Copy over the fields being collected
        for (let field of collectionFields) {
            collectedObj[field] = item[field];
        }
        
        targetObj[collectionName].push(collectedObj);
    }
    
    // Convert Map to final array
    return Array.from(collection.values());
}

This approach always stores address fields in an array format for consistency, even when there's only one address (resulting in an array of length 1). This simplifies data handling for users as they can reliably access the address information without complex logic to handle differing formats.

An example usage scenario with sample data:

let data = [
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 1,
    "address": "1234 address way"
  },
  {
    "user_id": 1,
    "name": "test name",
    "address_id": 2,
    "address": "5678 new address"
  },
  {
    "user_id": 2,
    "name": "diff name",
    "address_id": 1,
    "address": "1234 address way"
  }
];

let result = collateData(data, "user_id", "address", ["address", "address_id"]);
console.log(result);

If you prefer not to have single-element arrays, you can perform post-processing to extract individual elements:

// Function remains the same, only slight adjustment at the end for post-processing

let result = collateData(data, "user_id", "address", ["address", "address_id"]);

// Post-processing to remove single-element arrays
for (let item of result) {
    let array = item["address"];
    if (array.length === 1) {
        delete item["address"]; // Remove array
        Object.assign(item, array[0]); // Copy fields back to main object
    }
}

console.log(result);

Condense items into objects and arrays when the Express query yields multiple objects in a many-to-many query

Answer №1

Similar questions

What could be causing the malfunction of this particular express middleware error handling system?

Is there a method to prevent the json file from being constantly overwritten?

Creating a mesh parallel to the xy-plane in Three.js

Retrieving text from Node.js with the help of regular expressions

When executing a Javascript POST request to a PHP script, it succeeds when running on

Ionic utilized the $http service and was unexpectedly triggered two times

Validating alpha-numeric passwords with JavaScript

retrieve the timestamp when a user initiates a keystroke

What is the best way to display all mysql rows in Node.js using handlebars templating?

Ensure that the GraphQL field "x" with type "[User]" includes a selection of subfields. Perhaps you intended to specify "x{ ... }" instead

How to show line breaks in MySQL text type when rendering in EJS

Integrating objects into the <select> element through the combination of C#, JavaScript, and HTML connected to a SQL

A comprehensive guide on verifying user authentication and confirming sessionID creation using ReactJs and ExpressJs

Sending a JWT token to a middleware with a GET request in Express Node.js - the proper way

Issue with querying and ordering products in Django using Python when passing values through the GET request

Issues with fetching data using Axios API in an Express.js Node.js environment

The system cannot locate the module: Unable to find '@reactchartjs/react-chart-2.js'

Maintain the property characteristics (writable, configurable) following the execution of JSON.parse()

Storing the return value of a function call in a variable in node.js

Updating Values in Nested Forms with Angular Reactive Form