在postgresql中的同一JSON對像中合併一對多和一對一關係

小提琴連結： https ://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0

我的表格如下：

=> \d ops;
                                                  Table "public.ops"
         Column           |           Type           | Collation | Nullable |                      Default                       
---------------------------+--------------------------+-----------+----------+----------------------------------------------------
op_id                     | integer                  |           | not null | nextval('ops_op_id_seq'::regclass)
name                      | text                     |           |          | 
Indexes:
   "ops_pkey" PRIMARY KEY, btree (op_id)

=> \d events;
                                                  Table "public.events"
        Column         |         Type         | Collation | Nullable |                        Default                         
------------------------+----------------------+-----------+----------+--------------------------------------------------------
event_id               | integer              |           | not null | nextval('events_event_id_seq'::regclass)
desc                   | text                 |           |          | 
op_id                  | integer              |           |          | 
Indexes:
   "events_pkey" PRIMARY KEY, btree (event_id)

=> \d op_extra_info;
                                  Table "public.op_extra_info"
     Column       |  Type   | Collation | Nullable |                      Default                       
-------------------+---------+-----------+----------+----------------------------------------------------
op_extra_info_id  | integer |           | not null | nextval('op_extra_info_op_extra_info_id_seq'::regclass)
op_id             | integer |           |          | 
extra_info1       | text    |           |          | 
extra_info2       | text    |           |          | 
Indexes:
   "op_extra_info_pkey" PRIMARY KEY, btree (op_extra_info_id)

對於每個操作，可以有許多事件（每個事件通過“op_id”欄位引用其對應的操作）但只有一個額外的資訊（同樣，使用“op_id”來引用與其相關的操作）。我的目標是返回 JSON 結果，如下所示：

{ "name": ....,
 "extra_info": { "extra_info1": ....., "extra_info2": ..... },
 "events": [ { "desc": .... }, { "desc": .... }, ... ]
}

我已經能夠完成這個查詢：

SELECT to_json(x) FROM (
 SELECT ops.name, array_agg(events.descr) as events
   FROM ops
   LEFT JOIN (select event_id, op_id, descr FROM events) events USING(op_id)
   GROUP BY ops.name) AS x;

但我找不到辦法：

• 為“events”數組的每個元素獲取一個真實對象（即，一個真實的 {“descr”: …. } 對象，而不僅僅是“descr”列的值）
• 將 extra_info 數據添加為頂層“extra_info”鍵的子對象。

請參閱複合類型文件，了解如何將整個行作為單個值處理（最終作為 JSON 中的對象）。

顯然，為額外資訊獲取正確欄位名稱的唯一方法是為該子對象創建一個新類型：

CREATE TYPE extra_info_object AS (
 extra_info1 TEXT,
 extra_info2 TEXT
);

SELECT to_json(data)
FROM (SELECT name,
            ROW(extra_info1,
                extra_info2)::extra_info_object AS extra_info,
            (SELECT array_agg(events)
             FROM events
             WHERE events.op_id = ops.op_id
            ) AS events
     FROM ops
     LEFT JOIN op_extra_info USING (op_id)
) AS data;

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