By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- source data
CREATE TABLE test (
id INT,
user1Id VARCHAR(100),
user2Id VARCHAR(100),
status INT
);
INSERT INTO test (id,user1Id,user2Id,status) VALUES
(1,'a8t57h6p8n2efden','typ3vg6xb1vt7nw2',0),
(2,'cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl','a8t57h6p8n2efden',1),
(3,'0bw87kprb97pes1crom8ceodi07r2kd0','a8t57h6p8n2efden',2),
(4,'a8t57h6p8n2efden','ap21wzbew0bprt5t',0);
SELECT * FROM test;
Records: 4 Duplicates: 0 Warnings: 0
| id | user1Id | user2Id | status |
|---|---|---|---|
| 1 | a8t57h6p8n2efden | typ3vg6xb1vt7nw2 | 0 |
| 2 | cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl | a8t57h6p8n2efden | 1 |
| 3 | 0bw87kprb97pes1crom8ceodi07r2kd0 | a8t57h6p8n2efden | 2 |
| 4 | a8t57h6p8n2efden | ap21wzbew0bprt5t | 0 |
SET @user1Id := 'a8t57h6p8n2efden';
SET @userIdList := '[
"typ3vg6xb1vt7nw2",
"cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl",
"0bw87kprb97pes1crom8ceodi07r2kd0",
"absent value"
]';
SELECT jsontable.userid, test.status
FROM JSON_TABLE( @userIdList,
'$[*]' COLUMNS ( rowid FOR ORDINALITY,
userid VARCHAR(255) PATH '$'
)) jsontable
LEFT JOIN test
ON (@user1Id, jsontable.userid) IN ( (test.user1Id, test.user2Id),
(test.user2Id, test.user1Id)
)
| userid | status |
|---|---|
| typ3vg6xb1vt7nw2 | 0 |
| cy6mqqyykpldc2j1g5vm5cqsi6x1dgrl | 1 |
| 0bw87kprb97pes1crom8ceodi07r2kd0 | 2 |
| absent value | null |