postgresql - 从多个 postgres 表中选择数据的最佳实践

Question

我有包含以下表格的电影数据库:

电影:

 mid |  title     
-----+---------
  9  | Jason X

参与者:

mid |   name 
----+------------
 9  | Kane Hodder
 9  | Lexa Doig

我的问题是从所有表中获取与特定电影相关的所有数据的最佳做法是什么，在我看来，通过一个 sql 查询获取数据是理想的，因此我不必为每个表对数据库进行多次查询.

当我试图在一个查询中获取所有内容时，我得到了重复的数据，例如，我在每一行中都得到了 title 和 mid，而它只需要一次:

 mid | title   | name 
---------------+------------
  9  | Jason X | Kane Hodder
  9  | Jason X | Lexa Doig

webapp 会将 Postgres 输出转换为哈希值，如下所示:

movie => {mid => 9, title => 'Jason X', actors => ['Kane Hodger', 'Lexa doig']}

使用当前的 postgres 输出，我得到重复数据，例如 $movie{title} 等于 ['Jason X', 'Jason X']，仅在需要的地方 < strong>'杰森 X'

Answer 1

您可以使用 JSONB type:

select to_jsonb(m) || jsonb_build_object('actors', jsonb_agg(a.name)) as movies
from movies m
join actors a using(mid)
group by mid;

                                 movies                                 
------------------------------------------------------------------------
 {"mid": 9, "title": "Jason X", "actors": ["Kane Hodder", "Lexa Doig"]}
(1 row)

另见:

• JSON Functions and Operators
• Aggregate Functions

关于最佳实践的问题在 SO 上有点冒险，因为它可能被认为过于宽泛或基于意见。这在很大程度上取决于数据的大小。在大多数返回行数有限的简单情况下，使用json是很方便的。您还可以使用其他聚合函数，例如string_agg():

select m.*, string_agg(a.name, ',') as actors
from movies m
join actors a using(mid)
group by mid;

 mid |  title  |        actors         
-----+---------+-----------------------
   9 | Jason X | Kane Hodder,Lexa Doig
(1 row) 

如果应用程序一次处理的行数较大(例如，几十万)，那么更有效的解决方案可能是在客户端使用多个查询和数据处理。