sql - Selecting 2 tables

Question

How to select an attribute from another table with the original table

Answer 1

You should fix the data model. You should have three tables:

• users: one row per users
• groups: one row per groups
• user_groups: one row per user/group combination

With 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.