I've made a Select Query with multiple Tables using Joins. In the database, I have some data that have the same id but 1 or 2 columns have different values. When I run the query I get (for example) 2 objects that have the same data( for the first 3 columns) except a single column that has a different value. Now in the database, I have multiple of these, so I would get 10-20 objects and some of them are "duplicates" with the only difference being one of these columns. Is it possible to make a query that brings me this data without the "duplicates". So the columns that have the same value be the same but the column that the data is different be in an array (so have all 'different' data in the same place).
Example:
{
"user": "USER1",
"work": "Barista",
"first_name": "Ben",
"email": "ben@gmail.com",
"city": "Paris"
},
{
"user": "USER1",
"work": "Barista",
"first_name": "Ben",
"email": "ben@gmail.com",
"city": "Lyon"
}
So above I have 2 rows with the same data with only the city column being different so when I call the Query I get these 2 objects Would it be possible that instead, I get something like this either from the query or format the data after the query for several data?
{
"user": "USER1",
"work": "Barista",
"first_name": "Ben",
"email": "ben@gmail.com",
"city":
[
{
city1: Paris
},
{
city2: Lyon
}
]
}
Is it possible to query data like this from SQL or at least format it after multiple data bodies come from the query using javascript?
You cannot do it in plain SQL as it will always return data in rows. What you probably want is to use some kind of ORM (for example https://sequelize.org/).
You could do it with javascript but could get messy. Something like this(untested):
const newArray = [];
data.forEach((item) => {
const exists = newArray.find(
(el) =>
el.user === item.user &&
el.work === item.work &&
el.first_name === item.first_name
);
if (exists) {
exists.city.push(item.city);
} else newArray.push({ ...item, city: [item.city] });
});