'MySQL Combine Group by Column

I am new to SQL statements so my wording per my request may be incorrect, so I will provide a lot of detail to better understand my issue.

I have a database table called workouts that looks like this:

id bodyPart gifUrl name target broad_target
1 back http://d205bpvrqc9yn1.cloudfront.net/0007.gif alternate lateral pulldown lats back
2 chest http://d205bpvrqc9yn1.cloudfront.net/0009.gif assisted chest dip (kneeling) pectorals chest
3 lower legs http://d205bpvrqc9yn1.cloudfront.net/1708.gif assisted lying calves stretch calves legs
4 upper legs http://d205bpvrqc9yn1.cloudfront.net/1709.gif assisted lying glutes stretch glutes legs
5 upper legs http://d205bpvrqc9yn1.cloudfront.net/1710.gif assisted lying gluteus and piriformis stretch glutes legs
6 back http://d205bpvrqc9yn1.cloudfront.net/0015.gif assisted parallel close grip pull-up lats back

and I want it to combine all the broad_muscles together and wrap it under an array called data.

Ideally, it would look like this:

{
    title: 'Leg',
    data:[
        {
      "bodyPart": "lower legs",
      "equipment": "assisted",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/1708.gif",
      "id": "1708",
      "name": "assisted lying calves stretch",
      "target": "calves",
      "broad_target": "legs",
      "ppl": "legs"
    },
    {
      "bodyPart": "lower legs",
      "equipment": "smith machine",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/1396.gif",
      "id": "1396",
      "name": "smith toe raise",
      "target": "calves",
      "broad_target": "legs",
      "ppl": "legs"
    }
  ]
}

I will return it via. Lambda in Node.JS, so if needed, the answer can be how to parse it in Node.JS.

Thanks!

mysqlsqlnode.js


Solution 1:[1]

You can something do like this, but if your o/p formats in an array. You should use any SQL orms that can be helpful.

const sampleData = [{           
    "id":"1",
     "bodyPart":"back",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "alternate lateral pulldown" ,    
      "target": "lats",
      "broad_target" : "back"
},{
 "id":"2",
     "bodyPart":"chest",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "pectorals",
      "broad_target" : "chest"
},{
     "id":"2",
     "bodyPart":"upper legs",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "glutes",
      "broad_target" : "legs"
},{
     "id":"2",
     "bodyPart":"lower legs",
      "gifUrl": "http://d205bpvrqc9yn1.cloudfront.net/0007.gif",
      "name": "assisted chest dip (kneeling)" , 
      "target": "calves",
      "broad_target" : "legs"
}];

const matchString = "legs";

const output = sampleData.reduce((prev, current) => {
    if (current?.broad_target === matchString) {
        prev['title'] = "Leg";
        prev['data'] = (prev['data'] || []);
        prev['data'].push(current);
    }
    return prev
},{});

console.log(output);

Note: I have taken sample o/p as the sample the information might not match the actual data.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Apoorva Chikara

Related Questions