Get Join Table as Array of Results With Postgresql/Nodejs

get JOIN table as array of results with PostgreSQL/NodeJS

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

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

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

pool.query join array of data from different tables

I think this work...

router.get('/all', async (request, response) => {
    await pool.query(`SELECT * FROM app_advert LEFT JOIN public.users USING (id) ORDER BY username`, async (error, results) => {
        if (error) {
        throw error;
        }else{
            let adverts = [];
            let array = results.rows;
            array.forEach(function(item) {   
                pool.query(`SELECT name FROM app_advert_attachment LEFT JOIN file ON file.id=fk_file_id WHERE fk_advert_id=${advert.id}`)
                .then(res => {
                    // you process
                });
            })
        }
    })
});

SQL to only return table values found in array values

After tinkering with this a bit, I think I found the solution that works best for me and avoids creating temp tables:

SELECT value FROM table 
    JOIN UNNEST (array['foo', 'someval1', 'anotherval2', 'bar']) AS array_val
    ON array_val ~ value;

Interested in confirmation whether this indeed is simpler/more efficient than posted answers – or input on how it can be improved!

Retrieve One to Many Relationship Rows using array_agg in json format - postgresql

You need to fix your GROUP BY and use JSON_AGG():

SELECT i.id as interview_id, i.board, i.time_taken, i.notes, i.interview_date, 
       u.id as user_id, u.first_name, u.last_name, u.state, u.district, u.optional, 
       j.name as job, 
       json_agg(json_build_object('question', q.question, 'answer', q.answer, 'member', q."member", 'order', q."order")) as questions
FROM interview i LEFT JOIN
     question q
     ON q.interview_id = i.id JOIN
     users u
     ON i.user_id = u.id JOIN
     user_jobs uj
     ON uj.user_id = u.id JOIN
     job j
     ON uj.job_id = j.id
GROUP BY u.id, i.id, j.name

SQL join query (joined field as a json object)

Use postgresql's built in json_build_object function and create your own function like so:

CREATE FUNCTION get_req_w_cust(
  req_id_in int, 
  out results json
) AS $$
BEGIN
  SELECT json_build_object(
    'req_id', request_tbl.req_id,
    'details', request_tbl.details,
    'customer', json_build_object(
      'cust_id', customers_tbl.cust_id,
      'full_name', customers_tbl.full_name
    )
  ) INTO results
  FROM request_tbl, customers_tbl
    WHERE request_tbl.customer_id = customers_tbl.cust_id
    AND request_tbl.req_id = req_id_in
END
$$ language plpgsql;

Then you can access it from your code with

let {rows: [{results}] = await pg.query('select get_req_w_cust($1) AS results',[id]);

Alternatively just use the query without wrapping it in a function.

Returning nested JSON from many to many joined table from PostgreSQL to node.js

PostgreSQL does not return the JavaScript object you have posted. Your node driver is converting an array of arrays returned by PostgreSQL, which the driver is converting to JavaScript objects.

However, you can have PostgreSQL return a structure which I suspect will be converted how you wish by using array_agg.

Try this:

SELECT e.id,array_agg(t.label) AS label FROM exercise e LEFT JOIN tag_in_exercise te on e.id=te.exercise_id LEFT JOIN tag t on te.tag_id=t.id GROUP BY e.id;

You will get a raw PostgreSQL result in the structure you want, which hopefully the driver will translate as you intend:

id | label ----+----------------- 1 | {basic1,basic2} 2 | {NULL}

How to create a structure joined rows as nested documents PostgreSQL

I'm guessing as to your table structure here, but it should be close enough. So let's say this is your setup:

CREATE TABLE posts (id INTEGER, post_title TEXT, posted_at TEXT);

INSERT INTO posts
VALUES
(1, 'title 1', 'date 1'),
(2, 'title 2', 'date 2'),
(3, 'title 3', 'date 3');

CREATE TABLE comments (post_id INTEGER, comment_text TEXT, author_id INTEGER);

INSERT INTO comments
VALUES
(1, 'comment text 1', 34),
(3, 'comment text 3 a', 45),
(3, 'comment text 3 b', 67);

The simplest way to do it would be like so:

SELECT JSON_AGG(x ORDER BY id)
FROM (
  SELECT p.id, p.post_title, p.posted_at, JSON_AGG(c) AS comments
  FROM posts p
  LEFT JOIN comments c
      ON p.id = c.post_id
  GROUP BY p.id, p.post_title, p.posted_at
) x;

Which returns approximately the structure you want:

[{
    "id": 1,
    "post_title": "title 1",
    "posted_at": "date 1",
    "comments": [{
        "post_id": 1,
        "comment_text": "comment text 1",
        "author_id": 34
    }]
}, {
    "id": 2,
    "post_title": "title 2",
    "posted_at": "date 2",
    "comments": [null]
}, {
    "id": 3,
    "post_title": "title 3",
    "posted_at": "date 3",
    "comments": [{
        "post_id": 3,
        "comment_text": "comment text 3 a",
        "author_id": 45
    }, {
        "post_id": 3,
        "comment_text": "comment text 3 b",
        "author_id": 67
    }]
}]

I notice your comments array doesn't contain the reference to the parent post id. The best way to select only a subset of fields depends on your use case, but this should be a performant enough way:

SELECT JSON_AGG(x ORDER BY id)
FROM (
  SELECT
    p.id,
    p.post_title,
    p.posted_at,
    (
      SELECT JSON_AGG(c)
      FROM (
        SELECT c.comment_text, c.author_id
        FROM comments c
        WHERE c.post_id = p.id
      ) c
    ) AS comments
  FROM posts p
) x;

Returns

[{
    "id": 1,
    "post_title": "title 1",
    "posted_at": "date 1",
    "comments": [{
        "comment_text": "comment text 1",
        "author_id": 34
    }]
}, {
    "id": 2,
    "post_title": "title 2",
    "posted_at": "date 2",
    "comments": null
}, {
    "id": 3,
    "post_title": "title 3",
    "posted_at": "date 3",
    "comments": [{
        "comment_text": "comment text 3 a",
        "author_id": 45
    }, {
        "comment_text": "comment text 3 b",
        "author_id": 67
    }]
}]

Obviously the comments for post 2 differ between them, you'd have to put some logic to decide what you want no comments to look like.

Get Join Table as Array of Results With Postgresql/Nodejs