PostgreSQL and STRING_AGG

Question

I have two tables.

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
);

And data:

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');

For column search, I use the select query:

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;

The question is: how to make a query that will find and show all the document and all the doc_spec_sets.spec belonging to this document? I need a result like this:

--------------------------------------------------------------------------------------------------------------
|   | 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 |
--------------------------------------------------------------------------------------------------------------

Here's a working example: https://rextester.com/live/YOLY27213

Giuseppe Schembri · Accepted Answer · 2020-09-07 16:03:56Z

I just used your query without a where clause without doc_spec_sets.spec in the select.

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


*/

Collectives™ on Stack Overflow

PostgreSQL and STRING_AGG

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related