PostgreSQL无法聚合来自多个表的数据

吴维群

为了简单起见，我会在表中写最少的字段数。假设我有这个表:items，item_photos，item_characteristics。
create table items (  id               bigserial primary key, title            jsonb                                   not null,);create table item_photos (  id         bigserial primary key, path       varchar(1000)      not null, item_id    bigint references items (id) not null, sort_order smallint                     not null, unique (path,item_id));create table items_characteristics (  item_id                  bigint references items (id),    characteristic_id        bigint references characteristics (id), characteristic_option_id bigint references characteristic_options (id), numeric_value            numeric(19,2), primary key (item_id,characteristic_id), unique (item_id,characteristic_id,characteristic_option_id));我想总结一个项目的所有照片和特征。首先，我明白了这一点。
* I, n' h# z* t1 e* B0 vselect i.id                                                                              as id,      i.title                                                                           as title,      array_agg( ip.path)                                 as photos,      array_agg(            array [ico.characteristic_id,ico.characteristic_option_id,ico.numeric_value]) as characteristics_arrayFROM items i       LEFT JOIN item_photos ip on i.id = ip.item_id       LEFT JOIN items_characteristics ico on ico.item_id = i.idGROUP BY i.id这里的第一个问题是，如果item_characteristics有四个与项目相关的项目，例如item_photos没有条目，就在photos由四个空元素组成的字段中的数组{null,null,null,null}。所以我必须array_remove：
: ~8 Q: h7 O1 M* f; n& {array_remove(array_agg(ip.path),null)                                   as photos另外，如果我有一张照片和四张特征，我会得到四张照片的副本，比如 {img/test-img-1.png,img/test-img-1.png,img/test-img-1.png,img/test-img-1.png}
所以我不得不使用不同的：
( x7 H1 z$ J6 T& Farray_remove(array_agg(distinct ip.path),null)                                   as photos,array_agg(distinct         array [ico.characteristic_id,ico.characteristic_option_id,ico.numeric_value]) as characteristics_array这个决定对我来说很尴尬。我必须在那里item_characteristics再加两个字段，使情况变得复杂：
+ Y# o/ z1 ]+ ~, }string_value jsonb,--string valuejson_value jsonb --custom value因此，我需要聚合已经有-
" F5 b/ i7 {0 N" Q8 V1 w0 c/ vitem_characteristics，其中两个已经是了jsonb与众不同可能会对性能产生非常不利的影响。还有更优雅的解决方案吗？
                                                               
    解决方案: