我当前的查询在运行下面的示例时返回 expected O/P，我的问题是关于如何改进查询及其性能。
Schema SQL

CREATE TABLE function_groups (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(255) NOT NULL UNIQUE
);
INSERT INTO function_groups (name) VALUES ('f1.g1.a1');
INSERT INTO function_groups (name) VALUES ('f1.g1.a2');
INSERT INTO function_groups (name) VALUES ('f1.g1.a3');
INSERT INTO function_groups (name) VALUES ('f1.g1.a4');

INSERT INTO function_groups (name) VALUES ('f1.g2.a1');
INSERT INTO function_groups (name) VALUES ('f1.g2.a2');
INSERT INTO function_groups (name) VALUES ('f1.g2.a3');
INSERT INTO function_groups (name) VALUES ('f1.g2.a4');

INSERT INTO function_groups (name) VALUES ('f2.g1.a1');
INSERT INTO function_groups (name) VALUES ('f2.g1.a2');
INSERT INTO function_groups (name) VALUES ('f2.g1.a3');
INSERT INTO function_groups (name) VALUES ('f2.g1.a4');

INSERT INTO function_groups (name) VALUES ('f2.g2.a1');
INSERT INTO function_groups (name) VALUES ('f2.g2.a2');
INSERT INTO function_groups (name) VALUES ('f2.g2.a3');
INSERT INTO function_groups (name) VALUES ('f2.g2.a4');

预期O/P

id  groups
f1  [{"id": "f1.g1", "actions": [{"id": 1, "name": "f1.g1.a1"}, {"id": 2, "name": "f1.g1.a2"}, {"id": 3, "name": "f1.g1.a3"}, {"id": 4, "name": "f1.g1.a4"}]}, {"id": "f1.g2", "actions": [{"id": 5, "name": "f1.g2.a1"}, {"id": 6, "name": "f1.g2.a2"}, {"id": 7, "name": "f1.g2.a3"}, {"id": 8, "name": "f1.g2.a4"}]}]
f2  [{"id": "f2.g1", "actions": [{"id": 9, "name": "f2.g1.a1"}, {"id": 10, "name": "f2.g1.a2"}, {"id": 11, "name": "f2.g1.a3"}, {"id": 12, "name": "f2.g1.a4"}]}, {"id": "f2.g2", "actions": [{"id": 13, "name": "f2.g2.a1"}, {"id": 14, "name": "f2.g2.a2"}, {"id": 15, "name": "f2.g2.a3"}, {"id": 16, "name": "f2.g2.a4"}]}]

查询SQL

SELECT 
    SUBSTRING_INDEX(t1.name, '.', 1) AS id,
    (SELECT 
            JSON_ARRAYAGG(JSON_OBJECT('id',
                                t2.id,
                                'actions',
                                (SELECT 
                                        JSON_ARRAYAGG(JSON_OBJECT('id', t3.id, 'name', t3.name))
                                    FROM
                                        function_groups t3
                                    WHERE
                                        t2.id = SUBSTRING_INDEX(t3.name, '.', 2)
                                    GROUP BY t2.id)))
        FROM
            (SELECT 
                SUBSTRING_INDEX(t2.name, '.', 2) AS id
            FROM
                function_groups t2
            GROUP BY SUBSTRING_INDEX(t2.name, '.', 2)) t2
        WHERE
            SUBSTRING_INDEX(t2.id, '.', 1) = SUBSTRING_INDEX(t1.name, '.', 1)
        GROUP BY SUBSTRING_INDEX(t2.id, '.', 1)) AS groups
FROM
    function_groups t1
GROUP BY SUBSTRING_INDEX(t1.name, '.', 1)