PostgreSQL和nodejs/pg,返回嵌套的JSON [英] PostgreSQL and nodejs/pg, return nested JSON
问题描述
我正在使用带有nodejs和pg的PostgreSQL. 一切正常,但是我想从PostgreSQL作为嵌套的json输出结果-就像我在使用MongoDB或类似的东西一样.
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.
我来自PostgreSQL的2个表是:
My 2 tables from PostgreSQL are:
portfolio (id int, name text)
cars (portfolio_id int, name text);
是否有一种正确"的方法来返回具有以下结构的JSON对象:
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' }
}
我在nodejs/pg中查询数据库的一般方法是:
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 });
}
});
推荐答案
在PostgreSQL中,您可以构建以下JSON对象:
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
))
并包括cars.votes
个字段:
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
))
这篇关于PostgreSQL和nodejs/pg,返回嵌套的JSON的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!