Question 305176 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    @Bill it worked but now i want to add extra childs. I tried copying the select as t and change the fieldnames etc. but it did not work.

    (
  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

    I added this to the json_Object in the first SELECT statement.

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

    This is what i would like to create

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

  2. 0

    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);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search