1

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"}]
forpas
  • 160,666
  • 10
  • 38
  • 76
Akhil Rana
  • 145
  • 2
  • 12
  • #1. What is precise MySQL version? #2. Imagine that there is two more users `id=5` and `id=6` whose secondary user is `secondary_user=2`. What is desired output in this case? – Akina Apr 21 '22 at 10:54
  • "Imagine that there is two more users id=5 and id=6 whose secondary user is secondary_user=2". We dont consider as if it's already a secondary user. Mysql 2.18.1 – Akhil Rana Apr 21 '22 at 11:00
  • #1. What is precise MySQL version? #2. Your output value `[{"jac_jac", "1111"}]` is not valid JSON... do you need string output of this pattern? – Akina Apr 21 '22 at 11:04
  • MySQL 2.18.1. Out should be in this way [{username :"jac_jac", code : "1111"}] – Akhil Rana Apr 21 '22 at 11:06
  • *MySQL 2.18.1.* Not exists. Execute `SELECT VERSION();` and provide complete output. – Akina Apr 21 '22 at 11:26
  • MySQL 5.7.33-log – Akhil Rana Apr 21 '22 at 11:35
  • If so then the answer provided by @forpas is useful. Apply. – Akina Apr 21 '22 at 11:54

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • what would i do if need secondary user in this way [{username :"jac_jac", code : "1111"}]? Also thanks for you help it working. – Akhil Rana Apr 21 '22 at 11:32
  • @AkhilRana for this output there is a known problem in MySql which you can see here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=05100436fee518884830e66c18a9c15d Mysql changes the order of the items. This has been discussed in other questions in SO like this: https://stackoverflow.com/questions/48382457/mysql-json-column-change-array-order-after-saving – forpas Apr 21 '22 at 11:36
  • JSON_OBJECT(t2.username, t2.code) in demo it working fine but in workbench it throwing error "JSON documents may not contain null member names mysql". Why? – Akhil Rana Apr 21 '22 at 11:45
  • @AkhilRana why? Is username nullable? If it is then what result do you want for this case? – forpas Apr 21 '22 at 11:48