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

Error: Unable to locate module adaptivecards-templating

"Vue is failing to actively update an input that relies on changes from another

The concept of Puppeteer involves defining the browser and page in a synchronous manner

What could be causing this issue where the call to my controller is not functioning properly?

How can we stop the constant fluctuation of the last number on a number input field with a maxLength restriction

Is it possible to utilize Jquery in order to add an opening <tr> tag and a closing </tr> tag within a dynamic table?

Material Design Autocomplete search feature in Angular 2

Managing mouse click events in jQuery

The attempt to add a note with a POST request to the /api/notes/addnote endpoint resulted in a

Difficulties encountered when trying to load liquid using Javascript

Iteratively traverse the object to establish connections between parent and child elements

As I iterated over the Vehicles API data, I encountered an issue while trying to access specific Vehicle information. I received an error message stating "Cannot read property 'id' of undefined

Calculating the total of an array's values using JavaScript

The Consequences of Using Undeclared Variables in JavaScript

Would it be considered improper to implement an endless loop within a Vue.js instance for the purpose of continuously generating predictions with Tensorflow.js?

Tips for building a versatile fetch function that can be reused for various JSON formats within a React application

Printing Multiple Pages Using JavaScript and Cascading Style Sheets

The instance of the Javascript Object Prototype losing its reference

Error: The route cannot be established within an asynchronous function

I am encountering a problem with the app.patch() function not working properly. Although the get and delete functions are functioning as expected, the patch function seems to be