How to create JSON column with parent and multiple childs and grandchilds

I am new to MySQL and can't find the right answer so im hoping you guys can help me out.

I have 3 tables.

Applications (columns: appli_num (PK), appli_title)
Modules (columns: appli_num (PK), modu_num (PK), modu_title) and modules is a child from applications
Data_fields (columns: appli_num (PK), modu_num (PK), data_num(PK), data_title) and data_fields is a child from modules

I have tried many things but I can't figure out how to create the following JSON column.

[{
    "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"
            }
            ]
},
{
    "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"
            }
            ]
}
]

As a second column I only want to get the appli_num column.

Let me know if you need more information! Thanks in advance!

I have tried this and many more already and it did not meet my needs. link

Solution

When building JSON array aggregation with multiple levels of nesting, you must have multiple levels of subqueries to generate the aggregation at each level.

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);