How can i get data in one array using JSON_ARRAYAGG and JSON

This is my query, i'm using JSON_ARRAYAGG and JSON_OBJECT for hierarchy relations, but my concern is that i want data in following formated data. Don't be confuse with joins just help me get the data i want.

SELECT 
  CU.contact_group_id, 
  JSON_ARRAYAGG(
    JSON_OBJECT('contact_user', users.user)
  ) AS `contact_users` 
FROM 
  contact_user as CU 
  INNER JOIN (
    SELECT 
      U.id, 
      JSON_ARRAYAGG(
        JSON_OBJECT('id', U.id, 'user', U.first_name)
      ) as `user` 
    FROM 
      users as U 
) as `users` on CU.user_id = users.id

Data i get:

                "users": [
                            {
                                "contact_user": [
                                    {
                                        "id": 1,
                                        "user": "dash"
                                    },
                                    {
                                        "id": 3,
                                        "user": "dash1"
                                    }
                                ]
                            }
                        ]

Data i want:

                        "users": [
                                    {
                                        "id": 1,
                                        "user": "dash"
                                    },
                                    {
                                        "id": 3,
                                        "user": "dash1"
                                    }
                                ]

CodePudding user response：

Test this:

SELECT JSON_OBJECT('users', JSON_ARRAYAGG(JSON_OBJECT('id', users.id, 'user', users.first_name))) AS `users` 
FROM users
-- check if the following line is really needed
-- INNER JOIN contact_user on contact_user.user_id = users.id

CodePudding user response：

SELECT 
  CU.contact_group_id, 
  users.user AS `contact_users` 
FROM 
  contact_user as CU 
  INNER JOIN (
    SELECT 
      U.id, 
      JSON_ARRAYAGG(
        JSON_OBJECT('id', U.id, 'user', U.first_name)
      ) as `user` 
    FROM 
      users as U 
) as `users` on CU.user_id = users.id