Mysql Rows which have same values but in different column & Show them in a single row-CodePudding

What I want to do is merge the rows which have same values but in different column & Show them in a single row

I Tried to use the JSON_ARRAYAGG() but didn't get the results in my way

User Data

Here secondary user is the reference of primary_user

id	username	secondary_user	code
1	max_max	null	1356
2	jac_jac	1	1111
3	leo_leo	null	2222
4	bob_bob	3	4444

Result I want

id	username	secondary_user	code	secondary_users
1	max_max	null	1356	[{"jac_jac", "1111"}]
3	leo_leo	null	2222	[{"bob_bob", "4444"}]

CodePudding user response：

First you need a self join of the table.
Then use JSON_OBJECT() to create valid json objects for a user in the form of {"user_name": "user_code"} and not {"user_name", "user_code"} and finally aggregate and use JSON_ARRAYAGG():

SELECT t1.*,
       JSON_ARRAYAGG(JSON_OBJECT(t2.username, t2.code)) secondary_users
FROM tablename t1 LEFT JOIN tablename t2
ON t2.secondary_user = t1.id
WHERE t1.secondary_user IS NULL
GROUP BY t1.id;

I assume that id is the primary key of the table.

See the demo.