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!

Answer

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.