Postgres 11

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

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

CREATE TABLE


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

INSERT 0 3


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');

CREATE TABLE



INSERT 0 3


SELECT *
FROM   users    u
JOIN   projects p ON p.id = 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



SELECT 4


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,""}
{}



SELECT 3

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

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,""}	{}