在构建具有多层嵌套的JSON数组聚合时，必须有多层子查询才能在每一层生成聚合。

SELECT JSON_PRETTY(
  JSON_ARRAYAGG( 
    JSON_OBJECT(
      "modu_num", m.modu_num,
      "modu_title", m.modu_title,
      "data_fields", t.data_fields
    )
  ) 
) AS m
FROM (
  SELECT appli_num, modu_num,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        "data_num", data_num,
        "data_title", data_title
      )
    ) AS data_fields
  FROM Data_fields
  GROUP BY appli_num, modu_num
) AS t
JOIN Modules m USING (modu_num);

字符串

@Bill它工作了，但现在我想添加额外的查尔兹。我试着复制选择为t，并更改字段名等。但它没有工作。

(
  SELECT appli_num, modu_num,
    JSON_ARRAYAGG(
      JSON_OBJECT(
        "repo_num", repo_num,
        "repo_title", repo_title
      )
    ) AS reports
  FROM reports
  GROUP BY appli_num, modu_num
) AS r

字符串
我在第一个SELECT语句中将其添加到json_Object中。

SELECT JSON_PRETTY(
  JSON_ARRAYAGG( 
    JSON_OBJECT(
      "modu_num", m.modu_num,
      "modu_title", m.modu_title,
      "data_fields", t.data_fields,
      "reports", r.reports,
    )
  )

型
这就是我想要创造的

[{
    "modu_num": 1,
    "modu_title": "Module 1",
    "data_fields": [
            {
                "data_num": 1,
                "data_title": "data title 1"
            },
            {
                "data_num": 2,
                "data_title": "data title 2"
            },
            {
                "data_num": 3,
                "data_title": "data title 3"
            }
            ],
    "reports": [
            {
                "repo_num": 1,
                "repo_title": "repo title 1"
            }
            ]
},
{
    "modu_num": 2,
    "modu_title": "Module 2",
    "data_fields": [
            {
                "data_num": 4,
                "data_title": "data title 4"
            },
            {
                "data_num": 5,
                "data_title": "data title 5"
            },
            {
                "data_num": 6,
                "data_title": "data title 6"
            }
            ],
    "reports": [
            {
                "repo_num": 2,
                "repo_title": "repo title 2"
            }
            ]
}
]

型