如何在mongodb中组合组和项目

Question

这里我有一个sql查询，需要转换为mongo查询。sql查询:

select p.producer_id,p.name from producer p join
(select distinct ps.service_id,ps.value from service ps where ps.service_id=p.service_id) join
(select distinct pp.property_id,pp.property from property pp where  pp.service_id=p.service_id) 

My mongo query:

db.producer.aggregate([
{
"$lookup": {
"from": "service",
"localField": "producer_id",
"foreignField": "service_id",
"as": "ps"
}
},
{
"$unwind": "$ps"
},
{
"$lookup": {
"from": "property",
"localField": "producer_id,
"foreignField": "property_id",
"as": "pp"
}
},
{
"$unwind": "$pp"
},
{
"$group": {
"_id": {
"property_id": "$pp.property_id",
"service_id": "$ps.service_id"
}
}
},
{
"$project": {
"producer_id": "$p.producer_id",
"name": "$p.name",
"service_id":"$ps.service_id",
"value":"$ps.value",
"property_id":"$pp.property_id",
"property":"$pp.property",
"_id":0
}
}
]);

样例输入记录:制作人:

[{producer_id:1,name:'test'},{producer_id:2,name:'test2'}]

服务:

[{service_id:1,value:12},{service_id:1,value:13},{service_id:2,value:14}]

属性:

[{property_id:1,property:12},{property_id:1,property:56},{property_id:2,property:34}]

但是在输出中，我可以看到组的结果。当我试图从各自的集合中投影剩余的列("名称"，"值"，"属性"字段)时，这些值没有显示在输出中。这里，我必须选择不同的记录，并将不同的记录与其他记录一起显示。当我尝试在组中添加所有字段时("名称"，"值"，"属性"字段)，我能够看到所有记录，但性能很慢。有人能帮我一下吗?

Answer 1

在您的查询中，在$group阶段，您需要对您的额外字段

使用累加运算符

{
"$group": {
"_id": {
"property_id": "$pp.property_id",
"service_id": "$ps.service_id"
},
"field": {
"accumulator": "$value"
},
...
}
}

蓄电池操作符

试试这个:

db.producer.aggregate([
//make sure service.service_id is indexed for better performance
{
"$lookup": {
"from": "service",
"localField": "producer_id",
"foreignField": "service_id",
"as": "ps"
}
},
//make sure property.property_id is indexed for better performance
{
"$lookup": {
"from": "property",
"localField": "producer_id",
"foreignField": "property_id",
"as": "pp"
}
},
{
"$unwind": "$pp"
},
{
"$unwind": "$ps"
},
{
"$group": {
// SQL "distinct"
"_id": {
"service_id": "$ps.service_id",
"property_id": "$pp.property_id",
"value": "$ps.value",
"property": "$pp.property"
},
"producer_id": {
"$first": "$producer_id"
},
"name": {
"$first": "$name"
}
}
},
{
"$project": {
"_id": 0,
"producer_id": "$producer_id",
"name": "$name",
"service_id": "$_id.service_id",
"property_id": "$_id.property_id",
"value": "$_id.value",
"property": "$_id.property"
}
}
])

MongoPlayground