英文:
How to lookup a specific field of every object of an array using mongodb aggregation
问题
我试图查找rentalcontractdetails表中的每个rentalContractId。产品文档看起来像这样:
{
"productPrice": [
{
"rentalContractId": ObjectId("64018dfc8a7d37f378d6c427"),
"price": 20
},
{
"rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
"price": 10
}
]
}
我只需要查找rentalContractId。因此,在聚合中进行了以下查询以获得该输出:
db.products.aggregate([
{
$lookup: {
from: "rentalcontractdetails",
localField: "productPrice.rentalContractId",
foreignField: "_id",
as: "rentalContract",
},
}, { $set: {
"productPrice.rentalContract": "$rentalContract"
}}])
但这产生了这样的输出。
"productPrice":
[
{
"rentalContractId": "64018dfc8a7d37f378d6c427",
"price": 20,
"rentalContract": [
{
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
"contractDaysInNumber": 8,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
},
{
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
"contractDaysInNumber": 4,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
}
]
},
{
"rentalContractId": "64018dfc8a7d37f378d6c426",
"price": 10,
"rentalContract": [
{
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
"contractDaysInNumber": 8,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
},
{
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
"contractDaysInNumber": 4,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
}
]
}
]
预期输出:
{
"productPrice": [
{
"rentalContractId": {
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
},
"price": 20
},
{
"rentalContractId": {
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
}
"price": 10
}
]
}
英文:
I'm trying to lookup every rentalContractId from rentalcontractdetails table. The product document looks something like this:
{
"productPrice": [
{
"rentalContractId": ObjectId("64018dfc8a7d37f378d6c427"),
"price": 20
},
{
"rentalContractId": ObjectId("64018dfc8a7d37f378d6c426"),
"price": 10
}
]
}
I just need to lookup rentalContractId. So was doing the below query in aggregation to get that output,
db.products.aggregate([
{
$lookup: {
from: "rentalcontractdetails",
localField: "productPrice.rentalContractId",
foreignField: "_id",
as: "rentalContract",
},
}, { $set: {
"productPrice.rentalContract": "$rentalContract"
}}])
but this is producing output like this.
"productPrice":
[
{
"rentalContractId": "64018dfc8a7d37f378d6c427",
"price": 20,
"rentalContract": [
{
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
"contractDaysInNumber": 8,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
},
{
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
"contractDaysInNumber": 4,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
}
]
},
{
"rentalContractId": "64018dfc8a7d37f378d6c426",
"price": 10,
"rentalContract": [
{
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
"contractDaysInNumber": 8,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
},
{
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
"contractDaysInNumber": 4,
"createdAt": "2023-03-03T06:04:44.531Z",
"isDeleted": false,
"isArchived": false
}
]
}
]
expected output:
{
"productPrice": [
{
"rentalContractId": {
"_id": "64018dfc8a7d37f378d6c427",
"contractType": "8 - Days Rental",
},
"price": 20
},
{
"rentalContractId": {
"_id": "64018dfc8a7d37f378d6c426",
"contractType": "4 - Days Rental",
}
"price": 10
}
]
}
Thanks in advance.
答案1
得分: 1
一种选择是使用$lookup
与管道,并在$map
中使用$mergeObjects
更新您的$set
步骤:
db.products.aggregate([
{$lookup: {
from: "rentalcontractdetails",
localField: "productPrice.rentalContractId",
foreignField: "_id",
as: "rentalContract",
pipeline: [{$project: {contractType: 1}}]
}},
{$project: {
_id: 0,
productPrice: {$map: {
input: "$rentalContract",
in: {$mergeObjects: [
{rentalContractId: "$$this"},
{price: {$arrayElemAt: [
"$productPrice.price",
{$indexOfArray: ["$productPrice.rentalContractId", "$$this._id"]}
]}}
]}
}}
}}
])
了解它在playground示例中是如何工作的。
- mongodb的
$lookup
和mongoose的populate
是两种不同的东西,它们使用两种不同的机制。原始问题混合了这两种,但在代码中使用了$lookup
,所以我猜这就是意图。
英文:
One option is to use $lookup
with pipeline, and update your $set
step to use $mergeObjects
inside $map
:
db.products.aggregate([
{$lookup: {
from: "rentalcontractdetails",
localField: "productPrice.rentalContractId",
foreignField: "_id",
as: "rentalContract",
pipeline: [{$project: {contractType: 1}}]
}},
{$project: {
_id: 0,
productPrice: {$map: {
input: "$rentalContract",
in: {$mergeObjects: [
{rentalContractId: "$$this"},
{price: {$arrayElemAt: [
"$productPrice.price",
{$indexOfArray: ["$productPrice.rentalContractId", "$$this._id"]}
]}}
]}
}}
}}
])
See how it works on the playground example
- mongodb
$lookup
and mongoosepopulate
are two different things, working with two different mechanisms. The original question was mixing the two, but used$lookup
in the code, so I guess this was the intention.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论