By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (id int, user_id int, req_user_id int);
insert into t (id, user_id, req_user_id) values
(1, 1, 2),
(2, 2, 9),
(3, 7, 2);
Records: 3 Duplicates: 0 Warnings: 0
select * from t
| id | user_id | req_user_id |
|---|---|---|
| 1 | 1 | 2 |
| 2 | 2 | 9 |
| 3 | 7 | 2 |
select group_concat(id separator ',')
from (
select user_id as id from t
union
select req_user_id from t
) x
| group_concat(id separator ',') |
|---|
| 1,2,7,9 |
with cte as (
SELECT GROUP_CONCAT(DISTINCT CONCAT(user_id,',',req_user_id)) as ids
FROM t
WHERE req_user_id= 2 or user_id=2
)
SELECT JSON_OBJECTAGG(item,''), JSON_KEYS(JSON_OBJECTAGG(item,''))
FROM cte, JSON_TABLE(CONCAT('[', ids, ']'), '$[*]'
COLUMNS(
item TEXT PATH '$'
)
) as items;
| JSON_OBJECTAGG(item,'') | JSON_KEYS(JSON_OBJECTAGG(item,'')) |
|---|---|
| {"1": "", "2": "", "7": "", "9": ""} | ["1", "2", "7", "9"] |
with cte as (
SELECT GROUP_CONCAT(DISTINCT CONCAT(user_id,',',req_user_id)) as ids
FROM t
WHERE req_user_id= 2 or user_id=2
)
SELECT REGEXP_REPLACE(JSON_OBJECTAGG(item,''), '"|:| |\\{|\\}', '') as ids
FROM cte, JSON_TABLE(CONCAT('[', ids, ']'), '$[*]'
COLUMNS(
item TEXT PATH '$'
)
) as items;
| ids |
|---|
| 1,2,7,9 |