What's the syntax for doing a $lookup on a field that is an array of ObjectIds rather than just a single ObjectId?
Example Order Document:
{
_id: ObjectId("..."),
products: [
ObjectId("..<Car ObjectId>.."),
ObjectId("..<Bike ObjectId>..")
]
}
Not Working Query:
db.orders.aggregate([
{
$lookup:
{
from: "products",
localField: "products",
foreignField: "_id",
as: "productObjects"
}
}
])
Desired Result
{
_id: ObjectId("..."),
products: [
ObjectId("..<Car ObjectId>.."),
ObjectId("..<Bike ObjectId>..")
],
productObjects: [
{<Car Object>},
{<Bike Object>}
],
}
This question is related to
mongodb
mongodb-query
aggregation-framework
You can also use the pipeline stage to perform checks on a sub-docunment array
Here's the example using python (sorry I'm snake people).
db.products.aggregate([
{ '$lookup': {
'from': 'products',
'let': { 'pid': '$products' },
'pipeline': [
{ '$match': { '$expr': { '$in': ['$_id', '$$pid'] } } }
// Add additional stages here
],
'as':'productObjects'
}
])
The catch here is to match all objects in the ObjectId array (foreign _id that is in local field/prop products).
You can also clean up or project the foreign records with additional stages, as indicated by the comment above.
I have to disagree, we can make $lookup work with IDs array if we preface it with $match stage.
// replace IDs array with lookup results_x000D_
db.products.aggregate([_x000D_
{ $match: { products : { $exists: true } } },_x000D_
{_x000D_
$lookup: {_x000D_
from: "products",_x000D_
localField: "products",_x000D_
foreignField: "_id",_x000D_
as: "productObjects"_x000D_
}_x000D_
}_x000D_
])It becomes more complicated if we want to pass the lookup result to a pipeline. But then again there's a way to do so (already suggested by @user12164):
// replace IDs array with lookup results passed to pipeline_x000D_
db.products.aggregate([_x000D_
{ $match: { products : { $exists: true } } },_x000D_
{_x000D_
$lookup: {_x000D_
from: "products",_x000D_
let: { products: "$products"},_x000D_
pipeline: [_x000D_
{ $match: { $expr: {$in: ["$_id", "$$products"] } } },_x000D_
{ $project: {_id: 0} } // suppress _id_x000D_
],_x000D_
as: "productObjects"_x000D_
}_x000D_
}_x000D_
])Starting with MongoDB v3.4 (released in 2016), the $lookup aggregation pipeline stage can also work directly with an array. There is no need for $unwind any more.
This was tracked in SERVER-22881.
Aggregating with $lookup and subsequent $group is pretty cumbersome, so if (and that's a medium if) you're using node & Mongoose or a supporting library with some hints in the schema, you could use a .populate() to fetch those documents:
var mongoose = require("mongoose"),
Schema = mongoose.Schema;
var productSchema = Schema({ ... });
var orderSchema = Schema({
_id : Number,
products: [ { type: Schema.Types.ObjectId, ref: "Product" } ]
});
var Product = mongoose.model("Product", productSchema);
var Order = mongoose.model("Order", orderSchema);
...
Order
.find(...)
.populate("products")
...
use $unwind you will get the first object instead of array of objects
query:
db.getCollection('vehicles').aggregate([
{
$match: {
status: "AVAILABLE",
vehicleTypeId: {
$in: Array.from(newSet(d.vehicleTypeIds))
}
}
},
{
$lookup: {
from: "servicelocations",
localField: "locationId",
foreignField: "serviceLocationId",
as: "locations"
}
},
{
$unwind: "$locations"
}
]);
result:
{
"_id" : ObjectId("59c3983a647101ec58ddcf90"),
"vehicleId" : "45680",
"regionId" : 1.0,
"vehicleTypeId" : "10TONBOX",
"locationId" : "100",
"description" : "Isuzu/2003-10 Ton/Box",
"deviceId" : "",
"earliestStart" : 36000.0,
"latestArrival" : 54000.0,
"status" : "AVAILABLE",
"accountId" : 1.0,
"locations" : {
"_id" : ObjectId("59c3afeab7799c90ebb3291f"),
"serviceLocationId" : "100",
"regionId" : 1.0,
"zoneId" : "DXBZONE1",
"description" : "Masafi Park Al Quoz",
"locationPriority" : 1.0,
"accountTypeId" : 0.0,
"locationType" : "DEPOT",
"location" : {
"makani" : "",
"lat" : 25.123091,
"lng" : 55.21082
},
"deliveryDays" : "MTWRFSU",
"timeWindow" : {
"timeWindowTypeId" : "1"
},
"address1" : "",
"address2" : "",
"phone" : "",
"city" : "",
"county" : "",
"state" : "",
"country" : "",
"zipcode" : "",
"imageUrl" : "",
"contact" : {
"name" : "",
"email" : ""
},
"status" : "",
"createdBy" : "",
"updatedBy" : "",
"updateDate" : "",
"accountId" : 1.0,
"serviceTimeTypeId" : "1"
}
}
{
"_id" : ObjectId("59c3983a647101ec58ddcf91"),
"vehicleId" : "81765",
"regionId" : 1.0,
"vehicleTypeId" : "10TONBOX",
"locationId" : "100",
"description" : "Hino/2004-10 Ton/Box",
"deviceId" : "",
"earliestStart" : 36000.0,
"latestArrival" : 54000.0,
"status" : "AVAILABLE",
"accountId" : 1.0,
"locations" : {
"_id" : ObjectId("59c3afeab7799c90ebb3291f"),
"serviceLocationId" : "100",
"regionId" : 1.0,
"zoneId" : "DXBZONE1",
"description" : "Masafi Park Al Quoz",
"locationPriority" : 1.0,
"accountTypeId" : 0.0,
"locationType" : "DEPOT",
"location" : {
"makani" : "",
"lat" : 25.123091,
"lng" : 55.21082
},
"deliveryDays" : "MTWRFSU",
"timeWindow" : {
"timeWindowTypeId" : "1"
},
"address1" : "",
"address2" : "",
"phone" : "",
"city" : "",
"county" : "",
"state" : "",
"country" : "",
"zipcode" : "",
"imageUrl" : "",
"contact" : {
"name" : "",
"email" : ""
},
"status" : "",
"createdBy" : "",
"updatedBy" : "",
"updateDate" : "",
"accountId" : 1.0,
"serviceTimeTypeId" : "1"
}
}
Source: Stackoverflow.com