How can i get data in one array using JSON_ARRAYAGG and JSON_OBJECT in SQL query?

Question

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

Akina · Accepted Answer · 2022-09-27 12:19:07Z

1

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

answered Sep 27, 2022 at 12:19

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zain Ul Abideen · Accepted Answer · 2022-09-27 12:26:05Z

0

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

edited Sep 27, 2022 at 12:26

answered Sep 27, 2022 at 12:25

Zain Ul Abideen

33 bronze badges

Collectives™ on Stack Overflow

How can i get data in one array using JSON_ARRAYAGG and JSON_OBJECT in SQL query?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related