Postgres SQL: View with a nested table I have 2 tables users &amp; groups:
user_id | title         group_id | title  
----------------        ------------------
U_1     | User 1        G_1      | Group 1
U_2     | User 2        G_2      | Group 2
                        G_3      | Group 3

I have a mapping table between users and groups (a many to many relationship):
members
group_id | user_id | user_type
------------------------------
G_1      | U_1     | admin    
G_1      | U_2     | member   
G_2      | U_1     | admin    
G_3      | U_2     | admin    

I'm looking to create a materialized view with nested tables as follows
user_id | title  | groups            
----------------------------------------
U_1     | User 1 | group_id | user_type
        |        |----------------------
        |        | G_1      | admin    
        |        | G_2      | admin    
----------------------------------------
U_2     | User 2 | group_id | user_type
        |        |----------------------
        |        | G_1      | member   
        |        | G_3      | admin    

In my node.js app, I'm basically expecting the following
[
{
    &quot;user_id&quot;: &quot;U_1&quot;,
    &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_2&quot;,
    &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;}]
},
]

This was easy to achieve using MySQL (cast multiset), but I'm having trouble with postgres... Plz help...
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