MySQL 8.0

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

select version();

version()

8.0.30

CREATE TABLE jobcards (
    jobcardId varchar(255) ,
    advisorId text );

INSERT INTO jobcards  VALUES 
('f82d6c76-b344-4f58-8fe9-a405c9b968d1','[5d414796-935d-414d-8f7a-952c7806d7e3,627433fe-b6ca-465e-be66-f53cbc3c6d86]');

CREATE TABLE users (
    id varchar(255) ,
    name varchar(25) );

INSERT INTO users  VALUES 
('5d414796-935d-414d-8f7a-952c7806d7e3','Adam'),
('627433fe-b6ca-465e-be66-f53cbc3c6d86','Martin'),
('b6ca796t-judk-djdj-djdj-didkdkdksssk','Marry');

Records: 3  Duplicates: 0  Warnings: 0

SELECT * FROM jobcards;
SELECT * FROM users;

jobcardId
advisorId

f82d6c76-b344-4f58-8fe9-a405c9b968d1
[5d414796-935d-414d-8f7a-952c7806d7e3,627433fe-b6ca-465e-be66-f53cbc3c6d86]

id
name

5d414796-935d-414d-8f7a-952c7806d7e3
Adam

627433fe-b6ca-465e-be66-f53cbc3c6d86
Martin

b6ca796t-judk-djdj-djdj-didkdkdksssk
Marry

SELECT jobcardId, GROUP_CONCAT(U.name) name
FROM jobcards J
JOIN users U ON FIND_IN_SET(U.id,replace(replace(J.advisorId,'[',''),']',''))
GROUP BY J.jobcardId;

jobcardId
name

f82d6c76-b344-4f58-8fe9-a405c9b968d1
Adam,Martin

id	name
5d414796-935d-414d-8f7a-952c7806d7e3	Adam
627433fe-b6ca-465e-be66-f53cbc3c6d86	Martin
b6ca796t-judk-djdj-djdj-didkdkdksssk	Marry