我正在寻找一个答案，如何合并/联合/等2表内的同一个孩子在JSON_Object。
我有三张table。

• 模块
• data_fields（modu_id的子模块）
• 报告（modu_id指定的模块的子级）

CREATE TABLE modules (
  modu_id INT,
  appli_id INT,
  modu_title VARCHAR(50),
  t_modu_links INT
  
);
INSERT INTO modules VALUES (1,1,"Module 1",5);
INSERT INTO modules VALUES (2,1,"Module 2",6);

CREATE TABLE applications (
  appli_id INT,
  appli_title VARCHAR(50)
  
);
INSERT INTO applications VALUES (1,"application 1");
INSERT INTO applications VALUES (2,"application 2");

CREATE table data_fields (
  data_id INT,
  modu_id INT,
  data_title VARCHAR(50),
  t_data_links INT
  );
  
INSERT INTO data_fields VALUES(1,1,"Data field 1",1);
INSERT INTO data_fields VALUES(2,2,"Data field 2",2);
INSERT INTO data_fields VALUES(3,2,"Data field 3",1);

CREATE table reports (
  repo_id INT,
  modu_id INT,
  repo_title VARCHAR(50),
  t_repo_links INT
  );
  
INSERT INTO reports VALUES(1,1,"report 1",1);
INSERT INTO reports VALUES(2,2,"report 2",2);
INSERT INTO reports VALUES(3,2,"report 3",1);

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

字符串
我希望得到以下的回应。

{
    "modu_id": 1,
    "modu_title": "Module 1",
    "data": [
            {
                "id": 1,
                "title": "data field 1",
                "t_links": 1,
                "datatype": "datafield"
            },
            {
                "id": 1,
                "title": "report 1"
                "t_links": 1,
                "datatype": "report"
            }
            ]
},
{
    "modu_id": 2,
    "modu_title": "Module 2",
    "data": [
            {
                "id": 2,
                "title": "data field 2",
                "t_links": 2,
                "datatype": "datafield"
            },
            {
                "id": 3,
                "title": "data field 3",
                "t_links": 1,
                "datatype": "datafield"
            }
            {
                "id": 2,
                "title": "report 2"
                "t_links": 1,
                "datatype": "report"
            },
            {
                "id": 3,
                "title": "report 3"
                "t_links": 1,
                "datatype": "report"
            }
            ]
}

型
我知道我必须创建2个子查询来获得结果，但我正在努力将2个表放入1个json_object中。
我尝试了以下代码：

SELECT
    m.appli_id,
    a.appli_title,
    JSON_ARRAYAGG(
    JSON_OBJECT(
        "modu_id", m.modu_id,
        "modu_title", m.modu_title,
        "data", datafields)) as datafields,
    JSON_ARRAYAGG(
    JSON_OBJECT(
        "modu_id", m.modu_id,
        "modu_title", m.modu_title,
        "data", reports)) as reports,
    JSON_ARRAY_APPEND(JSON_ARRAYAGG(
    JSON_OBJECT(
        "modu_id", m.modu_id,
        "modu_title", m.modu_title,
        "data", datafields)),
        '$.data', 
    JSON_ARRAYAGG(
    JSON_OBJECT(
        "modu_id", m.modu_id,
        "modu_title", m.modu_title,
        "data", reports)))
FROM
(SELECT
    m.appli_id,
    m.modu_id,
    JSON_ARRAYAGG(
    JSON_OBJECT(
        "id", d.data_id,
        "title", d.data_title,
        "t_links", d.t_data_links,
        "Fieldtype", "datafield"
        )) as datafields
FROM modules as m
LEFT JOIN data_fields as d ON m.modu_id = d.modu_id
WHERE m.appli_id = 1
GROUP BY m.appli_id, m.modu_id
) as datafields

,
(SELECT
    m.appli_id,
    m.modu_id,
    JSON_ARRAYAGG(
    JSON_OBJECT(
        "id", r.repo_id,
        "title", r.repo_title,
        "t_links", r.t_repo_links,
        "Fieldtype", "report"
        )) as reports
FROM modules as m
LEFT JOIN reports as r ON m.modu_id = r.modu_id
WHERE m.appli_id = 1
GROUP BY m.appli_id, m.modu_id
) as reports
JOIN modules m USING(modu_id)
JOIN applications a ON a.appli_id = m.appli_id

GROUP BY m.appli_id

型
我知道这个代码是不正确的，因为它给了我两倍的记录。
希望你们能帮我。