我在查詢從我的資料庫中選擇一些資料時遇到問題，
我的資料庫有 3 個表：電影、演員、電影演員。
ACTORS TABLE
-----------
| ID | NAME |
-----------
| 1 | Bob |
-----------
| 2 | John |
-----------
MOVIES TABLE
-----------
| ID | TITLE|
-----------
| 1 | aaa |
-----------
| 2 | bbb |
-----------
MOVIES-ACTORS TABLE (MANY-TO-MANY)
--------------------
| MOVIE_ID| ACTOR_ID |
--------------------
| 1 | 1 |
--------------------
| 1 | 2 |
--------------------
| 2 | 1 |
--------------------
我需要獲取所有電影，并且在每部電影中我需要有一個名為 actor 的屬性，它應該是一個包含與該電影相關的所有演員的陣列。
回應應如下所示：
[
{id: 1, title: "aaa", actors: [{id: 1, name: "Bob"}, {id: 2, name: "John"}]},
{id: 2, title: "bbb", actors: [{id: 1, name: "Bob"}]}
]
實作此結果的最佳方法是什么？我可以通過 2 個不同的請求以及映射和過濾結果來做到這一點，但這對我來說似乎不合適。所以我嘗試使用 JOIN 撰寫一個查詢，但我只能為其中的每個演員獲取一個“電影”行。
我會分享它，因為我認為它離解決方案不遠。
SELECT movies.*, json_build_object('name', actor.name, 'id', actor.id) AS actors
FROM movies
LEFT OUTER JOIN movies_actors
ON movies.id = movies_actors.movie_id
LEFT OUTER JOIN actors
ON movies_actors.actor_id = actors.id
這就是我到目前為止所得到的：
[
{id: 1, title: "aaa", actors: {id: 1, name: "Bob"}},
{id: 1, title: "aaa", actors: {id: 2, name: "John"}},
{id: 2, title: "bbb", actors: {id: 1, name: "Bob"}}
]
uj5u.com熱心網友回復：
您需要（我假設它是主鍵）并用于GROUP BY構建您的演員物件：movies.idjson_agg
SELECT movies.*, json_agg(json_build_object('name', actors.name, 'id', actors.id)) AS actors
FROM movies
LEFT OUTER JOIN movies_actors
ON movies.id = movies_actors.movie_id
LEFT OUTER JOIN actors
ON movies_actors.actor_id = actors.id
GROUP BY movies.id
輸出（用于您的樣本資料）：
id title actors
1 aaa [{"name":"Bob","id":1},{"name":"John","id":2}]
2 bbb [{"name":"Bob","id":1}]
db-fiddle 上的演示
uj5u.com熱心網友回復：
到目前為止一切順利，您需要額外添加的是JSON_AGG()函式以及GROUP BY運算式，例如
SELECT JSON_AGG(result)
FROM
(
SELECT JSON_BUILD_OBJECT(
'id', m.id,
'title', m.title,
'actors',JSON_AGG(JSON_BUILD_OBJECT('id', a.id, 'name', a.name))
) AS result
FROM movies AS m
LEFT JOIN movies_actors AS ma
ON m.id = ma.movie_id
LEFT JOIN actors AS a
ON ma.actor_id = a.id
GROUP BY m.id, m.title
) AS j
Demo
uj5u.com熱心網友回復：
一步一步地“編織” JSON 結構，由內而外首先聚合演員的 id/name 對串列 ( ti)，然后聚合電影及其演員串列 ( tx)。
select json_agg(tx) from
(
select id, title,
(
select json_agg(ti) from
(
select id, "name"
from actors join movies_actors on actors.id = actor_id
where movie_id = movies.id
) ti
) as actors
from movies
) tx;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/510816.html