外部集合$查找结果中的项目特定字段

Question

我正在编写聚合，以便使用本地集合获取外部集合数据。

db.getCollection('orders').aggregate([
{
$match: {
status: "UNASSIGNED",
serviceLocationId: "83177"
}
}, {
$lookup: {
from: "servicelocations",
localField: "serviceLocationId",
foreignField: "serviceLocationId",
as: "locations"
}
}, {
$unwind: "$locations"
}])

我得到：

{
"_id" : ObjectId("59d32b5c360198e441b67545"),
"accountId" : 1.0,
"orderId" : "AQ137O1701240",
"serviceLocationId" : "83177",
"orderDate" : "2017-09-18T18:29:00.000Z",
"description" : "AQ137O1701240",
"serviceType" : "Delivery",
"orderSource" : "Import",
"takenBy" : "KARIM",
"plannedDeliveryDate" : ISODate("2017-10-09T00:00:00.000Z"),
"plannedDeliveryTime" : "",
"actualDeliveryDate" : "",
"actualDeliveryTime" : "",
"deliveredBy" : "",
"size1" : 25.0,
"size2" : 464.0,
"size3" : 46.0,
"jobPriority" : 1.0,
"cancelReason" : "",
"cancelDate" : "",
"cancelBy" : "",
"reasonCode" : "",
"reasonText" : "",
"status" : "UNASSIGNED",
"lineItems" : [ 
{
"ItemId" : "MMGW001",
"size1" : 25.0,
"size2" : 464.38,
"size3" : 46.875
}
],
"locations" : {
"_id" : ObjectId("59ce18e172dbf6926093e189"),
"accountId" : 1.0,
"serviceLocationId" : "83177",
"regionId" : "1",
"zoneId" : "DXBZONE1",
"description" : "EXPRESS BLUE MART SUPERMARKET",
"locationPriority" : 1.0,
"accountTypeId" : 1.0,
"locationType" : "SERVICELOCATION",
"location" : {
"makani" : "",
"lng" : 55.179042,
"lat" : 25.098741
},
"deliveryDays" : "MTWRFSU",
"serviceTimeTypeId" : "1",
"timeWindow" : {
"timeWindowTypeId" : "1"
},
"address1" : "",
"address2" : "",
"phone" : "",
"city" : "",
"county" : "",
"state" : "",
"country" : "",
"zipcode" : "",
"imageUrl" : "",
"contact" : {
"name" : "",
"email" : ""
},
"status" : "ACTIVE",
"createdBy" : "",
"updatedBy" : "",
"updateDate" : ""
}
}

但我需要：

{
"_id" : ObjectId("59d32b5c360198e441b67545"),
"accountId" : 1.0,
"orderId" : "AQ137O1701240",
"serviceLocationId" : "83177",
"orderDate" : "2017-09-18T18:29:00.000Z",
"description" : "AQ137O1701240",
"serviceType" : "Delivery",
"orderSource" : "Import",
"takenBy" : "KARIM",
"plannedDeliveryDate" : ISODate("2017-10-09T00:00:00.000Z"),
"plannedDeliveryTime" : "",
"actualDeliveryDate" : "",
"actualDeliveryTime" : "",
"deliveredBy" : "",
"size1" : 25.0,
"size2" : 464.0,
"size3" : 46.0,
"jobPriority" : 1.0,
"cancelReason" : "",
"cancelDate" : "",
"cancelBy" : "",
"reasonCode" : "",
"reasonText" : "",
"status" : "UNASSIGNED",
"lineItems" : [ 
{
"ItemId" : "MMGW001",
"size1" : 25.0,
"size2" : 464.38,
"size3" : 46.875
}
],
"locations" : {
"lng" : 55.179042,
"lat" : 25.098741
}
}

Answer 1

MongoDB小于3.4.4

基本上，使用$project作为最后阶段，并选择所有您想要的特定字段。不幸的是，$addFields已退出，因为它实际上会将子密钥与现有的子密钥"合并"。所以看起来很简单：

{ "$addFields": {
"locations": {
"lng": "$locations.location.lng",
"lat": "$locations.location.lat"    
}
}}

只是提供了"locations"下的所有现有内容以及那个些新定义的键。当然，除非你不在$lookup之后直接$unwind，如果这不会导致超过BSON限制，你可以这样做。(这称为$lookup+$unwind聚结)

然后我们可以将$addFields与$map一起使用，因为我们可以简单地"重新映射"数组：

{ "$addFields": {
"locations": {
"$map": {
"input": "$locations",
"as": "l",
"in": {
"lng": "$$l.location.lng",
"lat": "$$l.location.lat"
}
} 
}  
}},
{ "$unwind": "$locations" }

然后是$unwind，如果在重新映射后仍然需要的话。

因此，对于$project，它是：

{ "$project": {
"accountId" : 1,
"orderId" : 1,
"serviceLocationId" : 1,
"orderDate" : 1,
"description" : 1,
"serviceType" : 1,
"orderSource" : 1,
"takenBy" : 1,
"plannedDeliveryDate" : 1,
"plannedDeliveryTime" : 1,
"actualDeliveryDate" : 1,
"actualDeliveryTime" : 1,
"deliveredBy" : 1,
"size1" : 1,
"size2" : 1,
"size3" : 1,
"jobPriority" : 1,
"cancelReason" : 1,
"cancelDate" : 1,
"cancelBy" : 1,
"reasonCode" : 1,
"reasonText" : 1,
"status" : 1,
"lineItems" : 1,
"locations" : {
"lng": "$locations.location.lng",
"lat": "$locations.location.lat"    
}
}}

简单但冗长。

MongoDB 3.4.4或更高版本

如果您有带$objectToArray和$arrayToObject的MongoDB 3.4.4或更高版本，那么您可以对它更感兴趣：

{ "$replaceRoot": {
"newRoot": {  
"$arrayToObject": {
"$concatArrays": [
{ "$filter": {
"input": { "$objectToArray": "$$ROOT" },
"cond": { "$ne": [ "$$this.k", "locations" ] }
}},
{ "$objectToArray": {
"locations": {
"lng": "$locations.location.lng",
"lat": "$locations.location.lat"    
}
}}
]
}
}
}}    

它基本上从$$ROOT中获取当前整个文档中的所有字段，并将其转换为数组格式。然后，我们用"密钥名称"$filter和$concatArrays将"location"字段与新的"location"密钥和子密钥再次转换为数组。

最后，CCD_ 19当然将其转换回对象，该对象被提供给CCD_ 21的CCD_ 20作为最终输出。

---

因此，在$unwind之后使用除$addFields之外的任何一个，当然都会得到正确的结果：

/* 1 */
{
"_id" : ObjectId("59d32b5c360198e441b67545"),
"accountId" : 1.0,
"orderId" : "AQ137O1701240",
"serviceLocationId" : "83177",
"orderDate" : "2017-09-18T18:29:00.000Z",
"description" : "AQ137O1701240",
"serviceType" : "Delivery",
"orderSource" : "Import",
"takenBy" : "KARIM",
"plannedDeliveryDate" : ISODate("2017-10-09T00:00:00.000Z"),
"plannedDeliveryTime" : "",
"actualDeliveryDate" : "",
"actualDeliveryTime" : "",
"deliveredBy" : "",
"size1" : 25.0,
"size2" : 464.0,
"size3" : 46.0,
"jobPriority" : 1.0,
"cancelReason" : "",
"cancelDate" : "",
"cancelBy" : "",
"reasonCode" : "",
"reasonText" : "",
"status" : "UNASSIGNED",
"lineItems" : [ 
{
"ItemId" : "MMGW001",
"size1" : 25.0,
"size2" : 464.38,
"size3" : 46.875
}
],
"locations" : {
"lng" : 55.179042,
"lat" : 25.098741
}
}

---

MongoDB 3.6及更高版本

作为预览，$lookup对MongoDB 3.6进行了更具表现力的大修。因此，您实际上可以具体说明要以这种方式返回的字段：

{ "$lookup": {
"from": "servicelocations",
"let": { "serviceLocationId": "$serviceLocationId" },
"pipeline": [
{ "$match": { "$expr": { "$eq": [ "serviceLocationId", "$$serviceLocationId" ] } }},
{ "$project": {
"_id": 0,
"lng": "$location.lng",
"lat": "$location.lat"
}}
],
"as": "locations"
}}

当它真正发布时会更方便一点。这实际上使用$expr而不是localField和foreignField来定义子管道的$match阶段中的"联接"条件。然后，您可以简单地$project要返回的字段，然后这些字段进入$lookup所针对的数组。

展望未来，这是你想要采取的一般方法，因为它限制了实际返回的内容。