How to select an attribute from another table with the original table
See Question&Answers more detail:osHow to select an attribute from another table with the original table
See Question&Answers more detail:osYou should fix the data model. You should have three tables:
users
: one row per usersgroups
: one row per groupsuser_groups
: one row per user/group combinationWith your data model, I would use arrays to unpivot the values and then reaggregate:
I prefer arrays to JSON, so I would use:
select ug.type,
(select array_agg(u.name)
from (unnest(array[ug.user_1, ug.user_2, ug.user_3])) ar(user_id) join
users u
on u.user_id = u.id
) as users
from user_groups ug;
You can use a JSON function if you really want JSON.