$lookup on ObjectId's in an array

Question

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>}
  ],
}

Answer 1

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.

Answer 2

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.

Answer 3

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"
    }
}

Answer 4

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
db.products.aggregate([
    { $match: { products : { $exists: true } } },
    {
        $lookup: {
            from: "products",
            localField: "products",
            foreignField: "_id",
            as: "productObjects"
        }
    }
])

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
db.products.aggregate([
    { $match: { products : { $exists: true } } },
    {
        $lookup: {
            from: "products",
             let: { products: "$products"},
             pipeline: [
                 { $match: { $expr: {$in: ["$_id", "$$products"] } } },
                 { $project: {_id: 0} } // suppress _id
             ],
            as: "productObjects"
        }
    }
])

Answer 5

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")
    ...

Answer 6

Lookup is basically left join operation in relational database terms. So left document(table- in relation database terms) is the document where we are working currently and right document is the document from where we have to extract information.

Lookup->

   {
  $lookup:
     {
       from: <right document>,
       localField: <  field in left document which holds the info for right document >,
       foreignField: <field in the right document which is referenced in left document  >,
       as: <alias for array list where the result is to be stored.>
     }
}

So the right answer to above question is ->

{
"$lookup":
   {
        "from": "products",
        "localField": "products",
        "foreignField": "_id",
        "as": "productObject",
    }
}

But it wasn't working for me , I debugged and found the below stated issue: So, In my Case what I did wrong while making insert in left document was :

Wrong insert ->

product document -->

{
  "_id": ObjectId(something),
  "products":[ "some_id_1", "some_id_2"]
}

Right insert would be ->

Product Document

 {
  "_id": ObjectId(something),
  "products" : [ ObjectId("some_id_1"), ObjectId("some_id_2")]
 }

For me , I was storing the object id as string , not as in ObjectId object in the foreign key in left document. Pleasure ensure the right format while making an insert .

And in the end we all should learn from some one's mistake.