MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE tablename (
  `id` INTEGER PRIMARY KEY,
  `username` VARCHAR(7),
  `secondary_user` VARCHAR(4),
  `code` VARCHAR(4)
);
INSERT INTO tablename (`id`, `username`, `secondary_user`, `code`) VALUES
  ('1', 'max_max', null, '1356'),
  ('2', 'jac_jac', '1', '1111'),
  ('3', 'leo_leo', null, '2222'),
  ('4', 'bob_bob', '3', '4444'),
  ('5', 'mat_mat', '3', '9999');
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;


id
username
secondary_user
code
secondary_users


1
max_max
null
1356
[{"jac_jac": "1111"}]

3
leo_leo
null
2222
[{"mat_mat": "9999"}, {"bob_bob": "4444"}]

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