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…
Best Answer
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:
Given your sample data, the above returns:
Online example