在Node.js应用程序中,我们有从MongoDB迁移到PostgreSQL的要求.我们的Express API大量使用MongoDB aggregation
框架,我们必须用PostgreSQL重写它.例如,我们有以下形式的数据:
[{
"product": "PD1",
"application": "A",
"project": "PR1",
"version": "1"
},{
"product": "PD1",
"application": "A",
"project": "PR2",
"version": "3"
},{
"product": "PD1",
"application": "B",
"project": "PR3",
"version": "2"
},{
"product": "PD2",
"application": "C",
"project": "PR4",
"version": "5"
},{
"product": "PD2",
"application": "C",
"project": "PR5",
"version": "2"
}]
可以保证一个应用程序只属于一个产品.同样,一个项目只属于一个应用程序.我写了下面的MongoDB聚合查询来汇总这些数据:
let data = await CompanyModel.aggregate([{
$group: {
"_id": "$application",
"projects":{
$push: {
"project": "$project",
"version": "$version"
}
},
"product": {$first: "$product"}
}
},{
$group: {
"_id": "$product",
"applications":{
$push: {
"application": "$_id",
"projects": "$projects"
}
},
}
}])
上面的查询输出如下结果:
[{
"_id": "PD2",
"applications": [{
"application": "C",
"projects": [{ "project": "PR4", "version": "5" }, { "project": "PR5", "version": "2" }]
}]
},
{
"_id": "PD1",
"applications": [{
"application": "B",
"projects": [{ "project": "PR3", "version": "2" }]
}, { "application": "A", "projects": [{ "project": "PR1", "version": "1" }, { "project": "PR2", "version": "3" }] }]
}]
只以这种方式组织数据有一些很难的要求.
现在是主要的部分,我们如何用PostgreSQL编写一个查询,它的作用就像上面的查询一样,一个阶段的输出被输入到另一个阶段.我有一些使用SQL的经验,但从未使用过PostgreSQL.但我知道PostgreSQL支持JSON数据.我的猜测是,在SQL中,我会做如下操作:
SELECT * from (SELECT * from table_name group by field_name1) group by field_name2;
基本上,我正在try 判断PostgreSQL中是否存在这样的管道功能,但找不到.
在这方面我需要一些指导. 提前谢谢!