clear markdown compare help best fiddles feedback a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249019 fiddles created (32126 in the last week).

create table users (user_id int, project_ids text[]);
 hidden batch(es)

insert into users values (1, '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 ,b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11}') ,(2, '{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 ,"" ,e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}') ,(3, '{922475bb-ad93-43ee-9487-d2671b886479 ,""}'); -- 1st ID does not exist, 2nd is illegal
3 rows affected
 hidden batch(es)

create table projects (id uuid PRIMARY KEY, project_name text NOT NULL); insert into projects values ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'pro1'), ('b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11', 'pro2'), ('e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'pro3');
3 rows affected
 hidden batch(es)

SELECT * FROM users u JOIN projects p ON = ANY(array_remove(u.project_ids, '')::uuid[]);
user_id project_ids id project_name
1 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11} a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 pro1
1 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11} b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11 pro2
2 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,"",e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11} a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 pro1
2 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,"",e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11} e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11 pro3
 hidden batch(es)

SELECT * FROM users u LEFT JOIN LATERAL ( SELECT ARRAY( SELECT project_name -- use the actual column(s) of your case FROM unnest (array_remove(u.project_ids, '')::uuid[]) WITH ORDINALITY AS p(id, ord) JOIN projects USING (id) ORDER BY ord ) ) p(projects) ON true;
user_id project_ids projects
1 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11} {pro1,pro2}
2 {a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11,"",e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11} {pro1,pro3}
3 {922475bb-ad93-43ee-9487-d2671b886479,""} {}
 hidden batch(es)