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

byMR

November 24, 2023

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