Category: MongoDB

Joining Collections in MongoDB Queries using $lookup

Note: This only works in Mon­goDB 3.2 or lat­er, be sure to update if you need this functionality!

In sit­u­a­tions where you have an Objec­tID in a col­lec­tion and you want it resolved by Mon­goDB dur­ing your query, you can accom­plish this with aggre­gate and lookup.

Let’s say we had two col­lec­tions: insur­ance­Claim and insuranceProvider.

Here’s a stripped down exam­ple of insuranceClaims

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

Here’s a stripped down exam­ple of insurers:

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

If we want to have Mon­goDB resolve the insur­er when we query the claims, we can do so with the fol­low­ing query:

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

This would result in the fol­low­ing output:

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

Now we’ve effec­tive­ly had mon­go resolve the insur­ers for us!

If you have insur­ers in an array instead, there’s anoth­er step necessary.

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

To accom­plish the same in this sit­u­a­tion 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 pro­duce the fol­low­ing output:

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

Now that you’ve joined up the col­lec­tions, you prob­a­bly want to add in some fil­ters, to nar­row the list down to exact­ly what you want. To add a query into the mix sim­ply put the query into $match as fol­lows. 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"
        }
      }
    ]
  );

[amazon_link asins=‘1491954469,1785289985,1617291609,148420896X’ template=‘ProductCarousel’ store=‘openmindspace-20′ marketplace=‘US’ link_id=‘86735e82-d39e-11e6-9377–394c1b1958d6’]