PostgreSQL and STRING_AGG

Question

我有两个表。

CREATE TABLE document (
doc_id varchar PRIMARY KEY,
title varchar,
content varchar
);
CREATE TABLE doc_spec_sets (
tc_set_id varchar PRIMARY KEY,
doc_id varchar,
spec varchar
);

数据：

insert into document values('doc1', 'quickly for the visualiser that', 'with the structure I mentioned');
insert into document values('doc2', 'The main arguments', 'if your data was static');
insert into document values('doc3', 'We’ve had issues', 'columns so we need');
insert into document values('doc4', 'assessed our options', 'Replace both occurences');
insert into document values('doc5', 'So even though', 'full text of documents');
insert into doc_spec_sets values('tc1', 'doc1', 'documents');
insert into doc_spec_sets values('tc2', 'doc2', 'occurences');
insert into doc_spec_sets values('tc3', 'doc3', 'rather');
insert into doc_spec_sets values('tc4', 'doc1', 'options');
insert into doc_spec_sets values('tc5', 'doc2', 'documents');
insert into doc_spec_sets values('tc6', 'doc3', 'mentioned');
insert into doc_spec_sets values('tc7', 'doc1', 'options');
insert into doc_spec_sets values('tc8', 'doc2', 'structure');
insert into doc_spec_sets values('tc9', 'doc3', 'network');
insert into doc_spec_sets values('tc10', 'doc1', 'even');
insert into doc_spec_sets values('tc11', 'doc2', 'text');
insert into doc_spec_sets values('tc12', 'doc3', 'both');
insert into doc_spec_sets values('tc13', 'doc1', 'need');
insert into doc_spec_sets values('tc15', 'doc2', 'with');
insert into doc_spec_sets values('tc16', 'doc3', 'for');
insert into doc_spec_sets values('tc17', 'doc1', 'main');

对于列搜索，我使用选择查询：

select
document.doc_id,
document.title,
document.content,
doc_spec_sets.spec,
count(distinct doc_spec_sets.tc_set_id) as tc_count,
STRING_AGG(doc_spec_sets.spec,'-') AS agg_result 
from doc_spec_sets
LEFT OUTER join document on doc_spec_sets.doc_id = document.doc_id
where 
doc_spec_sets.spec = 'documents'
group by document.doc_id, doc_spec_sets.spec;

问题是：如何进行查询，以查找并显示属于该文档的所有文件和所有doc_spec_sets.spec？我需要这样的结果：

--------------------------------------------------------------------------------------------------------------
|   | doc_id | title      | content    | spec      | tc_count | agg_result                                   |
--------------------------------------------------------------------------------------------------------------
| 1 | doc1   | quickly... | with ...   | documents | 1        | documents, options, even, need, main         |
| 2 | doc2   | The main.. | if your... | documents | 1        | occurences, documents, structure, text, with |
--------------------------------------------------------------------------------------------------------------

下面是一个工作示例：https://rextester.com/live/YOLY27213

Answer 1

我刚才使用的查询没有where子句，select中没有doc_spec_sets.spec子句。

select
document.doc_id,
document.title,
document.content,
count(distinct doc_spec_sets.tc_set_id) as tc_count,
STRING_AGG(doc_spec_sets.spec,'-') AS agg_result 
from doc_spec_sets
LEFT join document on doc_spec_sets.doc_id = document.doc_id
group by document.doc_id;
/*
doc_id |              title              |            content             | tc_count |                agg_result                
--------+---------------------------------+--------------------------------+----------+------------------------------------------
doc1   | quickly for the visualiser that | with the structure I mentioned |        6 | documents-options-options-even-need-main
doc2   | The main arguments              | if your data was static        |        5 | occurences-structure-with-documents-text
doc3   | We’ve had issues                | columns so we need             |        5 | mentioned-network-rather-both-for

*/