0

I have 2 tables users & 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

[
{
    "user_id": "U_1",
    "title": "User 1",
    "groups": [{"group_id": "G_1","user_type": "admin"},{"group_id": "G_2","user_type": "admin"}]
},
{
    "user_id": "U_2",
    "title": "User 2",
    "groups": [{"group_id": "G_1","user_type": "member"},{"group_id": "G_3","user_type": "admin"}]
},
]

This was easy to achieve using MySQL (cast multiset), but I'm having trouble with postgres... Plz help...

0

1 Answer 1

3

There is no such thing as a "nested table" 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 | {"title": "User 1", "groups": [{"group_id": "G_1", "user_type": "admin"}, {"group_id": "G_2", "user_type": "admin"}], "user_id": "U_1"} 
U_2     | User 2 | {"title": "User 2", "groups": [{"group_id": "G_1", "user_type": "member"}, {"group_id": "G_3", "user_type": "admin"}], "user_id": "U_2"}

Online example

2
  • If data consistency is priority, views must be used instead of materialized views correct? And am I correct in saying views do not require refreshing because they are simply queries? Jan 5, 2021 at 19:30
  • Yes, that's correct
    – user1822
    Jan 5, 2021 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.