Postgres SQL：使用嵌套表查看

我有 2 個表使用者和組：

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

我有一個使用者和組之間的映射表（多對多關係）：

會員

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    

我正在尋找一個帶有嵌套表的物化視圖，如下所示

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    

在我的 node.js 應用程序中，我基本上期待以下內容

[
{
   "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"}]
},
]

使用 MySQL (cast multiset) 很容易實現，但我在使用 postgres 時遇到了問題…請幫助…

Postgres 中沒有“嵌套表”之類的東西，但您似乎只想要每個使用者的組分配的 JSON 表示。

以下查詢執行此操作：

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;

給定您的樣本數據，以上返回：

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"}

線上範例

引用自：https://dba.stackexchange.com/questions/282570