Using the $lookup operator in MongoDB 3.2

I often loiter over on the MongoDB User Google Group and there was an interesting question posted the other day. The poster wanted to form a document like this from two collections (where foo is a document from another collection)…

{
    "_id" : ObjectId("ObjectId of this bar"),
    "name" : "Bar Name",
    "foo" : {
      "_id" : ObjectId("56534720e2359196cf20f791"),
      "name" : "Foo Name"
    }
  }

Historically this would have been a multi-step operation.  We can now use the $lookup operator, in MongoDB 3.2, to achieve this in a single query using the aggregate framework. Here’s a quick demo on how to create the above document using the aggregate framework.

Insert some test data…

use test

db.foo.insert({"name": "Foo Name", "lookup_id": 1});
db.bar.insert({"name" : "Bar Name", "lookup_id": 1 });

With this data the aim is to pull a document in from the foo collection by matching the lookup_id field. We do this by running an aggregate on the bar collection using the $lookup operator.

db.bar.aggregate([
			{ "$match": { "lookup_id": 1 } },
			{ "$project": { "_id": 1, "name": 1, "lookup_id": 1 } },
			{ "$lookup": { "from": "foo",
					"localField": "lookup_id",
					"foreignField": "lookup_id",
					"as": "foo" }
				}
		]);

This produces the following document…

{
	"_id" : ObjectId("565b20ca7288e2c4e2b3b148"),
	"name" : "Bar Name",
	"lookup_id" : 1,
	"foo" : [
		{
			"_id" : ObjectId("565b20c97288e2c4e2b3b147"),
			"name" : "Foo Name",
			"lookup_id" : 1
		}
	]
}

Note how the foo document is contained within an array. This isn’t completely what the posted wanted but we can sort that out with the $unwind operator.

db.bar.aggregate([
			{ "$match": { "lookup_id": 1 } },
			{ "$project": { "_id": 1, "name": 1, "lookup_id": 1 } },
			{ "$lookup": { "from": "foo",
					"localField": "lookup_id",
					"foreignField": "lookup_id",
					"as": "foo" }
				},
			{ "$unwind": "$foo" },
			{ "$project": { "_id": 1, "name": 1, "foo._id": 1, "foo.name": 1 } }
		]);

This gives us our final document…

{
	"_id" : ObjectId("565b20ca7288e2c4e2b3b148"),
	"name" : "Bar Name",
	"foo" : {
		"_id" : ObjectId("565b20c97288e2c4e2b3b147"),
		"name" : "Foo Name"
	}
}