MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table account_refunds (
  id int,
  referrals JSON,
  accountID varchar(10)
);

insert into account_refunds values
(123,  '[1,2]', '001'),  
(124,  '[3]', '001'),  
(125,  '[4]', '001'),
(126,  '[5,6,7]', '001'),
(127, '[4]', '001');

Records: 5  Duplicates: 0  Warnings: 0


SELECT id, refID, accountID
FROM account_refunds
CROSS JOIN JSON_TABLE(
         referrals,
         "$[*]"
         COLUMNS(
           refID INT PATH "$"
         )
       ) t



id
refID
accountID


123
1
001

123
2
001

124
3
001

125
4
001

126
5
001

126
6
001

126
7
001

127
4
001


WITH CTE AS (
  SELECT accountID, refID, MIN(id) AS id
  FROM account_refunds
  CROSS JOIN JSON_TABLE(
         referrals,
         "$[*]"
         COLUMNS(
           refID INT PATH "$"
         )
       ) t
  GROUP BY accountID, refID
)
SELECT id, JSON_ARRAYAGG(refID) AS referrals, accountID
FROM CTE
GROUP BY accountID, id


id
referrals
accountID


123
[1, 2]
001

124
[3]
001

125
[4]
001

126
[5, 6, 7]
001