Postgres SQL: View with a nested table
There is no such thing as a &quot;nested table&quot; in Postgres, but it seems you just want a JSON representation of the group assignments for each user.
The following query does this:
select u.user_id, 
       u.title,
       jsonb_build_object(
              'user_id', u.user_id, 
              'title', u.title, 
              'groups', jsonb_agg(jsonb_build_object('group_id', g.group_id, 'user_type', m.user_type))) as groups
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by  u.user_id, u.title;

Given your sample data, the above returns:
user_id | title  | groups                                                                                                                                  
--------+--------+-----------------------------------------------------------------------------------------------------------------------------------------
U_1     | User 1 | {&quot;title&quot;: &quot;User 1&quot;, &quot;groups&quot;: [{&quot;group_id&quot;: &quot;G_1&quot;, &quot;user_type&quot;: &quot;admin&quot;}, {&quot;group_id&quot;: &quot;G_2&quot;, &quot;user_type&quot;: &quot;admin&quot;}], &quot;user_id&quot;: &quot;U_1&quot;} 
U_2     | User 2 | {&quot;title&quot;: &quot;User 2&quot;, &quot;groups&quot;: [{&quot;group_id&quot;: &quot;G_1&quot;, &quot;user_type&quot;: &quot;member&quot;}, {&quot;group_id&quot;: &quot;G_3&quot;, &quot;user_type&quot;: &quot;admin&quot;}], &quot;user_id&quot;: &quot;U_2&quot;}

Online example