How to query and get all data from relationships as an array of object? Mysql

Question

Let's say i have three tables
Main table

+----+------+---------------------+
| id | name | create_at           |
+----+------+---------------------+
|  1 | jack | 2023-04-26 22:01:37 |
|  2 | tom  | 2023-04-26 22:01:37 |
+----+------+---------------------+

Second table that has relation with main table

+----+----------------+--------+---------------------+
| id | companyuser_id | name   | create_at           |
+----+----------------+--------+---------------------+
|  1 |              1 | Amazon | 2023-04-26 22:47:21 |
+----+----------------+--------+---------------------+

Third table that has relation with second table

+----+------------+------------------------+---------------------+
| id | company_id | feedback               | create_at           |
+----+------------+------------------------+---------------------+
|  1 |          1 | i like this company    | 2023-04-26 22:58:04 |
|  2 |          1 | i dislike this company | 2023-04-26 22:58:04 |
+----+------------+------------------------+---------------------+

How do I query to get all data as an array of object like this?

  [
    {
        "user": "user1",
        "companies": [
            {
                "name": "company1",
                "feedback": [
                    {
                        "comment": "comment1",
                        "feedback_id": "1"
                    },
                    {
                        "comment": "comment2",
                        "feedback_id": "2"
                    }
                ]
            }
        ]
    }
  ]

this is what I have tried using JSON_ARRAY and JSON_OBJECT

SELECT
    JSON_ARRAY(
        JSON_OBJECT(
            "user",
            cu.name,
            "companies",
            JSON_ARRAY(
                JSON_OBJECT(
                    "name",
                    c.name,
                    "feedbacks",
                    JSON_ARRAY(
                        JSON_OBJECT(
                            "feedback",
                            f.feedback,
                            "id",
                            f.id
                        )
                    )
                )
            )
        )
    )
FROM companyuser as cu
    JOIN company c ON c.companyuser_id = cu.id
    JOIN feedback f ON f.company_id = c.id
GROUP BY cu.id;

However the result of feedbacks only return one instead of two Here's the output from the query that I have tried
Output:

[
    {
        "user": "jack",
        "companies": [
            {
                "name": "Amazon",
                "feedbacks": [
                    {
                        "id": 1,
                        "feedback": "i like this company"
                    }
                ]
            }
        ]
    }
]

The output I want is:

[
    {
        "user": "jack",
        "companies": [
            {
                "name": "Amazon",
                "feedbacks": [
                    {
                        "id": 1,
                        "feedback": "i like this company"
                    },
                    {
                        "id": 2,
                        "feedback": "i dislike this company"
                    },
                ]
            }
        ]
    }
]

Answer 1

You have one-to-many relationships between the tables, so you would need to aggregate them - that is, basically use json_arrayagg instead of json_array).

Here is one approach that uses correlated subqueries to build the nested objects:

select json_arrayagg(
    json_object(
        'user', cu.name,
        'companies', (
            select json_arrayagg(
                json_object(
                    'name', c.name, 
                    'feedback', (
                        select json_arrayagg(
                            json_object(
                                'comment', f.comment, 
                                'feedback_id', f.id
                            )
                        )
                        from feedback f 
                        where f.company_id = c.id
                    )
                ) 
            )
            from company c
            where c.companyuser_id = cu.id
        )
    ) 
) res
from companyuser cu

This generates a single-row resultset, with a single column called res that contains the JSON output.