MongoDB - Chris Case

Note: This only works in MongoDB 3.2 or later, be sure to update if you need this functionality!

In situations where you have an ObjectID in a collection and you want it resolved by MongoDB during your query, you can accomplish this with aggregate and lookup.

Let’s say we had two collections: insuranceClaim and insuranceProvider.

Here’s a stripped down example of insuranceClaims

{
    _id: ObjectId("5849ca6b7a5385ddcb2ea422"),
    insurer: ObjectId("584998f690b755f6a9fc3750"),
    filed: false
}

Here’s a stripped down example of insurers:

{
    _id: ObjectId("584998f690b755f6a9fc3750"),
    name: 'foo'
}

If we want to have MongoDB resolve the insurer when we query the claims, we can do so with the following query:

db.getCollection('insuranceClaims')
    .aggregate(
      [
        {
          "$lookup": {
            "from": "insurers", 
            "localField": "insurer", 
            "foreignField": "_id", 
            "as": "insurer_loaded"
          }
        }
      ]
    );

This would result in the following output:

{
    _id: ObjectId("5849ca6b7a5385ddcb2ea422"),
    insurer: ObjectId("584998f690b755f6a9fc3750"),
    filed: false,
    insurer_loaded: {
        _id: ObjectId("584998f690b755f6a9fc3750"),
        name: 'foo'
    }
}

Now we’ve effectively had mongo resolve the insurers for us!

If you have insurers in an array instead, there’s another step necessary.

{
    _id: ObjectId("5849ca6b7a5385ddcb2ea422"),
    filed: false,
    insurers: [{ObjectId("584998f690b755f6a9fc3750")}]
}

To accomplish the same in this situation we’ll use $unwind on the array.

db.getCollection('insuranceClaims')
  .aggregate(
    [
      {"$unwind": "$insurers"},
      {
        "$lookup": {
          "from": "insurers", 
          "localField": "insurers", 
          "foreignField": "_id", 
          "as": "insurer_loaded"
        }
      }
    ]
  );

This would produce the following output:

{
    _id: ObjectId("5849ca6b7a5385ddcb2ea422"),
    insurer: ObjectId("584998f690b755f6a9fc3750"),
    filed: false,
    insurer_loaded: [{
        _id: ObjectId("584998f690b755f6a9fc3750"),
        name: 'foo'
    }]
}

Now that you’ve joined up the collections, you probably want to add in some filters, to narrow the list down to exactly what you want. To add a query into the mix simply put the query into $match as follows. This query will load up claims where the field filed is false.

  .aggregate(
    [
      {"$match": {"filed": false}},
      {"$unwind": "$insurers"},
      {
        "$lookup": {
          "from": "insurers", 
          "localField": "insurers", 
          "foreignField": "_id", 
          "as": "insurer_loaded"
        }
      }
    ]
  );

Category: MongoDB

Joining Collections in MongoDB Queries using $lookup