Return to Answer

API: map, any, task, batch

UPDATE

The following related answer offers more options, by concatenating child queries, which will give a much improved performance: Combine nested loop queries to parent result pg-promise.

The same as above, but using ES7 async/await syntax:

await db.task(async t => { const questions = await t.any('SELECT * FROM questions'); for(const q of questions) { q.votes = await t.any('SELECT id, value FROM votes WHERE question_id = $1', [q.id]); } return questions; }); // method "task" resolves with the correct data tree 

API: map, any, task, batch

UPDATE-1

The following related answer offers more options, by concatenating child queries, which will give a much improved performance: Combine nested loop queries to parent result pg-promise.

UPDATE-2

Another example added, using ES7 async/await approach.

This is easy to do with pg-promise:

function buildTree(t) { const v = q => t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id) .then(votes => { q.votes = votes; return q; }); return t.map('SELECT * FROM questions', [], v).then(t.batch); } db.task(buildTree) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

API: map, any, task, batch

Related questions:

• Get a parents + children tree with pg-promise
• Conditional task with pg-promise

And if you want to use just a single query, then using PostgreSQL 9.4 and later syntax you can do the following:

SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

And then your pg-promise example would be:

const query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; const data = await db.map(query, [], a => a.json); 

And you definitely will want to keep such complex queries in external SQL files. See Query Files.

Conclusion

The choice between the two approaches presented above should be based on the performance requirements of your application:

• The single-query approach is faster, but is somewhat difficult to read or extend, being fairly verbose
• The multi-query approach is easier to understand and to extend, but it is not great for performance, due to dynamic number of queries executed.

UPDATE

The following related answer offers more options, by concatenating child queries, which will give a much improved performance: Combine nested loop queries to parent result pg-promise.

This is easy to do with pg-promise:

function buildTree(t) { const v = q => t.any('SELECT id, value FROM votes WHERE question_id = $1', q.id) .then(votes => { q.votes = votes; return q; }); return t.map('SELECT * FROM questions', undefined, v).then(a => t.batch(a)); } db.task(buildTree) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

API: map, any, task, batch

Related questions:

• Get a parents + children tree with pg-promise
• Conditional task with pg-promise

And if you want to use just a single query, then using PostgreSQL 9.4 and later syntax you can do the following:

SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

And then your pg-promise example would be:

const query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; const data = await db.map(query, [], a => a.json); 

And you definitely will want to keep such complex queries in external SQL files. See Query Files.

Conclusion

The choice between the two approaches presented above should be based on the performance requirements of your application:

• The single-query approach is faster, but is somewhat difficult to read or extend, being fairly verbose
• The multi-query approach is easier to understand and to extend, but it is not great for performance, due to dynamic number of queries executed.

UPDATE

The following related answer offers more options, by concatenating child queries, which will give a much improved performance: Combine nested loop queries to parent result pg-promise.

SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

const query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; db.map(query, [], a => a.json) .then(data => { console.log(data); // your data tree }) .catch(error => { console.log(error); }); 

SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) FROM questions q 

const query = `SELECT json_build_object('id', q.id, 'content', q.content, 'votes', (SELECT json_agg(json_build_object('id', v.id, 'value', v.value)) FROM votes v WHERE q.id = v.question_id)) json FROM questions q`; const data = await db.map(query, [], a => a.json);