How to Efficiently Retrieve Questions and Their Associated Votes as a Single JSON Object in PostgreSQL with NodeJS?

Retrieving Questions and Votes in PostgreSQL and NodeJS

This article explores efficient methods for fetching questions and their associated votes as a single JSON object using PostgreSQL and NodeJS. We'll examine several approaches, weighing their performance implications.

The application scenario involves users creating questions and casting votes (upvotes or downvotes). The goal is to retrieve each question along with an array of its votes.

Method 1: Multiple Queries (pg-promise)

This approach uses pg-promise to execute multiple queries. First, it retrieves all questions. Then, for each question, it fetches the corresponding votes.

function buildTree(t) {
    const v = q =>
        t
            .any('SELECT id, value FROM votes WHERE question_id = ', 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); 
    })
    .catch((error) => {
        console.log(error);
    });

Alternatively, using ES7 async/await:

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 = ', [q.id]);
    }
    return questions;
});

Method 2: Single Query (PostgreSQL JSON Functions)

PostgreSQL 9.4 and later offer a more efficient single-query solution using JSON functions:

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;

This query constructs a JSON object for each question, including an aggregated array of votes. With pg-promise:

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

Performance Comparison

The single-query approach (Method 2) is significantly faster due to reduced database round trips. However, Method 1 (multiple queries) offers better readability and maintainability, especially for more complex scenarios.

For optimal performance with large datasets, consider techniques like concatenating child queries to minimize database interactions, as discussed in related resources on combining nested loop queries.

The above is the detailed content of How to Efficiently Retrieve Questions and Their Associated Votes as a Single JSON Object in PostgreSQL with NodeJS?. For more information, please follow other related articles on the PHP Chinese website!