我有两个系列,水果和推销员。我希望我的查询返回所有以逗号分隔的业务员的水果。
推销员文档具有水果ID数组
水果文档具有ID,名称,........推销员表具有ID,名称,水果[apple_id,mango_id .......],...
db.getCollection('fruit').aggregate([{ "$unwind": "$fruits" }, { "$lookup": {
"from": "salesman",
"localField": "fruits",
"foreignField": "_id",
"as": "fruitObjects"
}},
{ "$unwind": "$fruitObjects" } ])
甚至这个查询也没有给出$ fruitObjects ..的结果?
水果文件
{
"_id" : ObjectId("5b101caddcab7850a4ba32eb"),
"name" : "Mango"
}
{
"_id" : ObjectId("5b101caddcab7850a4ba32ec"),
"name" : "Pears"
}
{
"_id" : ObjectId("5b101caddcab7850a4ba32de"),
"name" : "apple"
}
{
"_id" : ObjectId("5b101caddcab7850a4ba32fe"),
"name" : "guava"
}
推销员文件
{
"_id" : ObjectId("5b101caddcab7850a4ba3257"),
"name" : "xyz",
"fruits":["5b101caddcab7850a4ba32ec","5b101caddcab7850a4ba32de","5b101caddcab7850a4ba32fe"]
}
{
"_id" : ObjectId("5b101caddcab7850a4ba3258"),
"name" : "abc",
"fruits":["5b101caddcab7850a4ba32eb","5b101caddcab7850a4ba32de"]
}
{
"_id" : ObjectId("5b101caddcab7850a4ba3259"),
"name" : "def",
"fruits":["5b101caddcab7850a4ba32ec"]
}
{
"_id" : ObjectId("5b101caddcab7850a4ba3260"),
"name" : "zxc",
"fruits":["5b101caddcab7850a4ba32ec","5b101caddcab7850a4ba32de","5b101caddcab7850a4ba32eb"]
}
``````````````````````````
是的@barrypicker是正确的-字段类型不匹配,导致上为空salesman
,请尝试存储相同类型的字段。同时,您实际上可以将一种转换为其他类型-每次查询时,也不需要这样做$unwind
,请尝试以下查询:
查询1:
db.fruit.aggregate([
{
$lookup:
{
from: "salesman",
let: { fruitName: { $toString: '$_id' } },
pipeline: [
{
$match:
{
$expr:
{ $in: ["$$fruitName", "$fruits"] }
}
}, { $project: { name: 1, _id: 0 } }
],
as: "salesman"
}
}])
查询1的结果:
/* 1 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32eb"),
"name" : "Mango",
"salesman" : [
{
"name" : "abc"
},
{
"name" : "zxc"
}
]
}
/* 2 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32ec"),
"name" : "Pears",
"salesman" : [
{
"name" : "def"
},
{
"name" : "zxc"
}
]
}
/* 3 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32de"),
"name" : "apple",
"salesman" : [
{
"name" : "abc"
},
{
"name" : "zxc"
}
]
}
/* 4 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32fe"),
"name" : "guava",
"salesman" : []
}
或者,如果您希望这些名称位于数组中:
查询2:
db.fruit.aggregate([
{
$lookup:
{
from: "salesman",
let: { fruitName: { $toString: '$_id' } },
pipeline: [
{
$match:
{
$expr:
{ $in: ["$$fruitName", "$fruits"] }
}
}, { $project: { name: 1, _id: 0 } }
],
as: "salesmanList"
}
}, {
$project: {
name: 1, salesman: {
$map:
{
input: "$salesmanList",
as: "each",
in: '$$each.name'
}
}
}
}])
查询2的结果:
/* 1 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32eb"),
"name" : "Mango",
"salesman" : [
"abc",
"zxc"
]
}
/* 2 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32ec"),
"name" : "Pears",
"salesman" : [
"def",
"zxc"
]
}
/* 3 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32de"),
"name" : "apple",
"salesman" : [
"abc",
"zxc"
]
}
/* 4 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32fe"),
"name" : "guava",
"salesman" : []
}
水果收集:
/* 1 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32eb"),
"name" : "Mango"
}
/* 2 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32ec"),
"name" : "Pears"
}
/* 3 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32de"),
"name" : "apple"
}
/* 4 */
{
"_id" : ObjectId("5b101caddcab7850a4ba32fe"),
"name" : "guava"
}
推销员集合:
/* 1 */
{
"_id" : ObjectId("5b101caddcab7850a4ba3258"),
"name" : "abc",
"fruits" : [
"5b101caddcab7850a4ba32eb",
"5b101caddcab7850a4ba32de"
]
}
/* 2 */
{
"_id" : ObjectId("5b101caddcab7850a4ba3259"),
"name" : "def",
"fruits" : [
"5b101caddcab7850a4ba32ec"
]
}
/* 3 */
{
"_id" : ObjectId("5b101caddcab7850a4ba3260"),
"name" : "zxc",
"fruits" : [
"5b101caddcab7850a4ba32ec",
"5b101caddcab7850a4ba32de",
"5b101caddcab7850a4ba32eb"
]
}
如果你想从整个对象salesman
,那么你可以删除{ $project: { name: 1, _id: 0 } }
在$lookup
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句