PostgreSQL and nodejs/pg, return nested JSON

Question

I'm using PostgreSQL with nodejs and pg. Everything works fine, but I want to output a result from PostgreSQL as a nested json - as if I was working with MongoDB or similar.

My 2 tables from PostgreSQL are:

portfolio (id int, name text)

cars (portfolio_id int, name text);

Is there a "correct" way of returning a JSON object with the following structure:

{
    { name: 'Portfolio #1', cars: { name: 'Car #1', name: 'Car #2' },
    { name: 'Portfolio #2', cars: { name: 'Car #3' }
}

My general way of querying the database in nodejs/pg is:

client.query('SELECT ...', [params], function(err, result) {
    done();
    if (err) {
        res.status(500).json({ error: err });
    } else {
        res.json({ portfolios: result.rows });
    }
});

Indicate

Answer 1

In PostgreSQL, you can build the following JSON object:

[
    { "name": "Portfolio #1", "cars": [ "Car #1", "Car #2" ] },
    { "name": "Portfolio #2", "cars": [ "Car #3" ] }
]

You could construct the object from your tables with the following query:

select array_to_json(array(
  select row_to_json(n)
  from portfolio p
  left join lateral (select p.name, array(select name from cars where portfolio_id = p.id) as cars) n on true
  ))

And with cars.votes fields included:

select array_to_json(array(
  select row_to_json(n)
  from portfolio p
  left join lateral (select p.id, p.name, array_to_json(array(
     select row_to_json((select a from (select c.name, c.votes) a))
     from cars c
     where portfolio_id = p.id)) as cars) n on true
  ))

Licketysplit

I'm missing the "name: " in the cars, and are trying to get 2 parameters for my cars out: pastebin.com/GR0jb6VK. However, it fails with "...subquery must return only one column". Can you help? – Indicate

I think it's with the "... SELECT car_name, votes FROM..." – Indicate

@MichaelNielsen I updated the answer with cars.votes in it. The query is significantly more complex, and would grow in complexity the more stuff you want to add into it. – Licketysplit

Thank you. Would it be easier or more "correct" to output simple JOINS, and then merge the JSON rows via Javascript/nodejs/pg afterwards? – Indicate