GROUP_CONCAT to return an object array in a many to many relationship

I am working on a mySQL query for a personal project. Right now the end goal is to have the query return the following json for each item:

{
    "id": 1,
    "designName": "Slender Man",
    "designNotes": "Rewrite the lapel component",
    "quantity": 3,
    "colors": [
        {
        "id": 4,
        "colorName": "black",
        "colorSwatch": "rgb (0,0,0)",
        "brandName": "caron simply soft",
        "yarnWeightId": {
              "weightNumber": 4,
              "weightName": "medium/worst weight"
          }
        },{
        "id": 5,
        "colorName": "Off White",
        "colorSwatch": "rgb (255,255,255)",
        "brandName": "caron simply soft",
        "yarnWeightId": {
              "weightNumber": 4,
              "weightName": "medium/worst weight"
          }
        }
    ]
}

My most recent attempt is:

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    d.isDeleted,
    GROUP_CONCAT(
        JSON_OBJECT(
            c.colorName,
            c.colorSwatch,
            c.brandName,
            yw.weightNumber,
            yw.weightName
        )
    ) AS colorsUsed
FROM designs AS d
    INNER JOIN design_colors AS dc 
        ON dc.designId = d.id
    INNER JOIN colors AS c 
        ON dc.colorId = c.id
    INNER JOIN yarnweights AS yw 
        ON c.yarnWeightId = yw.id
GROUP BY d.id
;

Which is giving me an internal server error. Testing it with a simplier query goes through fine so I’ve narrowed it down to the query itself being the issue.

The query below got me close:

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    d.isDeleted,
    GROUP_CONCAT(
        DISTINCT c.id
        GROUP BY c.id
    ) AS colorsUsed
FROM designs AS d
    INNER JOIN design_colors AS dc 
        ON dc.designId = d.id
    INNER JOIN colors AS c 
        ON dc.colorId = c.id
    INNER JOIN yarnweights AS yw 
        ON c.yarnWeightId = yw.id
GROUP BY
    d.id

It returned objects like this:

{
    "id": 3,
    "designName": "Slenderman",
    "designNotes": null,
    "quantity": 0,
    "isDeleted": 0,
    "colorsUsed": "4,5"
}

But while I get the ids of all the colorsUsed I do not get them in an array, let alone the array of objects I need there. I feel like GROUP_CONCAT isn’t the right solution for this, or at least that I’m not using it correctly, but it’s what comes up when I’ve been trying to find the solution. Just a point in the right direction to keep looking would be helpful too.

---

My database is structured as so: [db diagram][https://i.stack.imgur.com/lxsvv.png]

Answer

Instead of using string concatenation function such as GROUP_CONCAT(), you can generate a valid JSON object using MySQL JSON functions. JSON_OBJECT() can be used to create objects, and JSON_ARRAYAGG() is an aggregate functions that generates JSON arrays.

First, let’s start with a query that returns all needed columns:

SELECT 
    d.id,
    d.designName,
    d.designNotes,
    d.quantity,
    c.id,
    c.colorName,
    c.colorSwatch,
    c.brandName
    y.weightNumber,
    y.weightName
FROM 
    designs AS d
    INNER JOIN design_colors AS dc ON dc.designId = d.id
    INNER JOIN colors AS c ON dc.colorId = c.id
    INNER JOIN yarnweights AS y ON c.yarnWeightId = y.id

Now we can turn on aggregation an use the JSON functions to generate the expected resultset:

SELECT JSON_OBJECT(
    'id', d.id,
    'designName', d.designName,
    'designNotes', d.designNotes,
    'quantity', d.quantity,
    'colors', JSON_ARRAYAGG(
        JSON_OBJECT(
            'id', c.id,
            'colorName', c.colorName,
            'colorSwatch', c.colorSwatch,
            'brandName', c.brandName
            'yarnWeightId', JSON_OBJECT(
                'weightNumber', y.weightNumber,
                'weightName', y.weightName
            )
        )
    ) AS myjson
FROM 
    designs AS d
    INNER JOIN design_colors AS dc ON dc.designId = d.id
    INNER JOIN colors AS c ON dc.colorId = c.id
    INNER JOIN yarnweights AS y ON c.yarnWeightId = y.id
GROUP BY
    d.id,
    d.designName,
    d.designNotes,
    d.quantity

This returns a resultset with a unique column, where each record contains the expected JSON object.