访问 nodejs、mysql 和 json 中的嵌套对象数组

我有这些桌子:

CREATE TABLE Progress_Category (
    categoryId int(4) AUTO_INCREMENT NOT NULL,
    name varchar(150) NOT NULL,
    PRIMARY KEY (categoryId)
);
CREATE TABLE Progress_Skill (
    skillId int(4) AUTO_INCREMENT NOT NULL,
    name varchar(150) NOT NULL,
    currentProgress int NOT NULL,
    `25` varchar(300) NOT NULL,
    `50` varchar(300) NOT NULL,
    `75` varchar(300) NOT NULL,
    `100` varchar(300) NOT NULL,
    categoryId int(4) NOT NULL,
    PRIMARY KEY (skillId),
    CONSTRAINT Constr_Progress_Skill_Skill_fk FOREIGN KEY Skill_fk (categoryId) REFERENCES Progress_Category(categoryId) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Progress_Message (
    messageId int(4) AUTO_INCREMENT NOT NULL,
    message varchar(500) NOT NULL,
    messageDate DATE NOT NULL,
    skillId int(4) NOT NULL,
    PRIMARY KEY (messageId),
    CONSTRAINT Constr_Progress_Message_Message_fk FOREIGN KEY Message_fk (skillId) REFERENCES Progress_Skill(skillId) ON DELETE CASCADE ON UPDATE CASCADE
);

我使用这个查询来检索表中的所有数据:

SELECT *
    FROM Progress_Category AS pcat
        LEFT JOIN Progress_Skill AS ps
            ON pcat.categoryId = ps.catParentId
        LEFT JOIN Progress_Message AS pm
            ON ps.skillId = pm.skillParentId

对于类别中的每一项技能，都将创建一行新的类别，其中包含受人尊敬的技能.对于技能的每一条消息，将创建一个新的包含类别和技能的行，并带有受尊重的消息.

查询结果:

+------------+-----------+---------+-----------+-----------------+------+-------+--------+-------+-------------+-----------+-------------------------+-------------+---------------+
| categoryId | catname   | skillId | skillname | currentProgress | 25   | 50    | 75     | 100   | catParentId | messageId | message                 | messageDate | skillParentId |
+------------+-----------+---------+-----------+-----------------+------+-------+--------+-------+-------------+-----------+-------------------------+-------------+---------------+
|          1 | Languages |       1 | Spanish   |             100 | Read | Write | Listen | Speak |           1 |         1 | Native language         | 2022-08-27  |             1 |
|          1 | Languages |       2 | English   |              85 | Read | Write | Listen | Speak |           1 |         2 | Learning since 2016     | 2022-08-27  |             2 |
|          1 | Languages |       2 | English   |              85 | Read | Write | Listen | Speak |           1 |         3 | Can speak almost fluent | 2022-08-27  |             2 |
|          2 | Projects  |    NULL | NULL      |            NULL | NULL | NULL  | NULL   | NULL  |        NULL |      NULL | NULL                    | NULL        |          NULL |
|          3 | Ideas     |    NULL | NULL      |            NULL | NULL | NULL  | NULL   | NULL  |        NULL |      NULL | NULL                    | NULL        |          NULL |
+------------+-----------+---------+-----------+-----------------+------+-------+--------+-------+-------------+-----------+-------------------------+-------------+---------------+
5 rows in set (0.001 sec)

在NodeJS中，我使用该查询和以下代码:

connection.query(myquery, function(err, results, fields) {
    if (err) {
        console.log('----> Error with MySQL query in /api/showProgress: ' + err.message);
    }
    else{
        console.log('Query successful, results are being displayed.');
            
        var categories = [];
        for (let category in results) {
            if(categories.length > 0){
                for(let key in categories){
                    if(results[category].categoryId !== categories[key].Category.Id){
                        console.log("Category Id: " + results[category].categoryId + " Id already in the array: " + categories[key].Category.Id);
                        categories.push({
                            "Category" : [{
                                "Id" : results[category].categoryId,
                                "Name" : results[category].catname
                            }]
                        });
                    }
                }
            }
        }
        else{
            categories.push({
                "Category" : [{
                    "Id" : results[category].categoryId,
                    "Name" : results[category].catname
                }]
            })
        }
    }
    response.send({"My progress" : categories});
});

我得到的结果是:

Query successful, results are being displayed.
Category Id: 1 Id already in the array: undefined
Category Id: 1 Id already in the array: undefined
Category Id: 1 Id already in the array: undefined
Category Id: 2 Id already in the array: undefined
Category Id: 2 Id already in the array: undefined
Category Id: 2 Id already in the array: undefined
Category Id: 2 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined
Category Id: 3 Id already in the array: undefined

所以问题是categories[key].Category.Id.我不知道如何访问数组中属于Category的属性Id.

最后一个 idea 是，如果是这样的话，只有一个类别与一组技能一起显示，而不是类别、技能、相同类别、其他技能:

当前:

{"My progress":[
    {
        "Category":[{
            "Id":1,
            "Name":"Languages",
            "Skill":"asd"
        }]
    },
    {
        "Category":[{
            "Id":1,
            "Name":"Languages",
            "Skill":"fgh"
        }]
    },
    {
        "Category":[{
            "Id":1,
            "Name":"Languages",
            "Skill":"ijk"
        }]
    },
]}

预期:

{"My progress":[
    {
        "Category":[
            {
                "Id":1,
                "Name":"Languages",
                "Skills":[{
                    "Name":"asd",
                    "Name":"fgh",
                    "Name":"ijk"
                }]
            },
            {
                "Id":2,
                "Name":"Projects",
                "Skills":[{
                    "Name":"123",
                    "Name":"456",
                    "Name":"789"
                }]
            }
        ]
    }
]}