Is it possible to query data like this from SQL?

Question

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?

yes you build json in mysql direct or you use javascript for that — nbk, Jan 12 '22 at 14:31
You'll need to define which field determines uniqueness (probably `user`?). You could _mostly_ get this data from MySQL directly if you do a GROUP_CONCAT like https://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql. Your data would come back like `"Paris, Lyon"` and you'd still need to process it in JS if you want these values to be separate objects, but it minimizes the amount of duplicate data you're returning which is good. — WOUNDEDStevenJones, Jan 12 '22 at 14:38
there are the json functions https://dev.mysql.com/doc/refman/8.0/en/json.html and the rest is a select that can get the data from the two table, but tsill you always have to provide a [mre] — nbk, Jan 12 '22 at 14:41

score 3 · Accepted Answer · answered Jan 12 '22 at 14:35

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

score 0 · Answer 2 · answered Jan 12 '22 at 14:33

0

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/).

answered Jan 12 '22 at 14:33

Krupson

134
5

Is it possible to query data like this from SQL?

2 Answers2