Using MongoDB $lookup for Graph-like/Join-like Requests

When making database queries, you'll frequently need to look up items in other tables/collections based on a previously fetched item. For example, if you want to fetch the data needed to display a social media post, you first need to fetch the post data, then take the user ID from the post data to fetch the user's profile picture, username, etc.
In SQL, this can be done with joins:
SELECT *
FROM Posts
LEFT JOIN Users
ON Posts.UserID=Users.ID;
In GraphQL, you simply add the relevant field to your query:
{
	post {
		body
		image
		user {
			username
			image
		}
	}
}
MongoDB, on the other hand, is neither a relational nor graph-based database: it's a document-based one. The standard way to make requests is for one document at a time (example code below using mongoose):
const post = await PostModel.findOne({ _id: postId });
const user = await UserModel.findOne({ _id: post.userId });
The problem here is that two requests are being made to MongoDB instead of one. Two is still a reasonable number, but this number quickly grows as your app becomes more complicated. In Postulate post pages, for example, four requests are needed to render the page, for the project owner, project, post, and post author:
const thisOwner = await UserModel.findOne({ username: username });

if (!thisOwner) return { notFound: true };

const thisProject = await ProjectModel.findOne({ userId: thisOwner._id, urlName: projectUrlName });

if (!thisProject) return { notFound: true };

const thisPost = await PostModel.findOne({ projectId: thisProject._id, urlName: encodeURIComponent(postUrlName) });

if (!thisPost) return { notFound: true };

if (thisPost.privacy === "private") {
	const session = await getSession(context);

	if (!session) return { notFound: true };

	const isOwner = session.userId === thisPost.userId.toString();
	const isCollaborator = thisProject.collaborators.map(d => d.toString()).includes(session.userId);

	if (!isOwner && !isCollaborator) return { notFound: true };
}

let thisAuthor = thisOwner;

// if collaborator, fetch collaborator object
if (thisPost.userId !== thisAuthor._id.toString()) {
	thisAuthor = await UserModel.findOne({ _id: thisPost.userId });
}

return { props: { postData: cleanForJSON(thisPost), projectData: cleanForJSON(thisProject), thisOwner: cleanForJSON(thisOwner), thisAuthor: cleanForJSON(thisAuthor), key: postUrlName }};
This amount of requests quickly becomes unreasonably slow when navigating between pages. Thankfully, there's a way to condense all these requests into one query, replicating the join and graph structures of SQL and GraphQL. To do so, we can use MongoDB aggregation pipelines and $lookup operators.
Here's a dummy example using that post-user example from earlier:
const graphObj = await PostModel.aggregate([
	{$match: { "_id": new mongoose.Types.ObjectId(postId) }},
	{
		$lookup: {
			from: "users",
			localField: "userId",
			foreignField: "_id",
			as: "userArr",
		},
	},
]});
graphObj will now contain the data of the selected post, as well as the author attached to it, all in one query!
Lookups are even more powerful because you can chain aggregation pipelines together, going multiple levels deep, and using multiple criteria for matching documents. Here's that four-query Postulate example from earlier as a single lookup query:
const graphObj = await UserModel.aggregate([
	{$match: { "username": username }},
	{
		$lookup: {
			from: "posts",
			let: {"userId": "$_id"},
			pipeline: [
				{$match: {$expr: {$and: [{$eq: ["$userId", "$$userId"]}, {$eq: ["$urlName", encodeURIComponent(postUrlName)]}]}}},
				{
					$lookup: {
						from: "projects",
						let: {"projectId": "$projectId"},
						pipeline: [
							{$match: {$expr: {$eq: ["$_id", "$$projectId"]}}},
							{
								$lookup: {
									from: "users",
									localField: "userId",
									foreignField: "_id",
									as: "ownerArr",
								}
							}
						],
						as: "projectArr",
					}
				}
			],
			as: "postArr",
		}
	},
]);

// return 404 if missing object at any stage
if (!graphObj.length || !graphObj[0].postArr.length || !graphObj[0].postArr[0].projectArr.length || !graphObj[0].postArr[0].projectArr[0].ownerArr.length) {
	return { notFound: true };
}

const thisAuthor = graphObj[0];
const thisPost = thisAuthor.postArr[0];
const thisProject = thisPost.projectArr[0];
const thisOwner = thisProject.ownerArr[0];

return { props: { postData: cleanForJSON(thisPost), projectData: cleanForJSON(thisProject), thisOwner: cleanForJSON(thisOwner), thisAuthor: cleanForJSON(thisAuthor), key: postUrlName }};
Lookups can also be used to make calculations like document counts, dates, etc. more efficient by running them on the database rather than the frontend of the backend. For example, Postulate uses the following request to get snippet and post counts and dates:
const thisProjectArr = await ProjectModel.aggregate([
	{
	    $sort: {
	        "updatedAt": -1,
	    }
	},
	{
	    $lookup: {
	        from: "posts",
	        let: {"projectId": "$_id"},
	        pipeline: [
	            { $match:
	                    { $expr:
	                            { $eq: ["$projectId", "$$projectId"] }
	                    }
	            },
	            { $count: "count" }
	        ],
	        as: "posts"
	    }
	},
	{
	    $lookup: {
	        from: "snippets",
	        let: {"projectId": "$_id"},
	        pipeline: [
	            { $match:
	                    { $expr:
	                            { $eq: ["$projectId", "$$projectId"] }
	                    }
	            },
	            { $count: "count" }
	        ],
	        as: "snippets"
	    }
	},
	{
	    $lookup: {
	        from: "snippets",
	        let: {"projectId": "$_id"},
	        pipeline: [
	            { $match:
	                    { $expr:
	                            { $and: [
	                                    { $eq: ["$projectId", "$$projectId"] },
	                                    { $ne: ["$linkedPosts", []] }
	                                ]}
	                    }
	            },
	            { $count: "count" }
	        ],
	        as: "linkedSnippets"
	    }
	}
	{ $match: {_id: mongoose.Types.ObjectId(projectId)} },
	...aggregatePipeline,
	{
	    $lookup: {
	        from: "posts",
	        let: {"projectId": "$_id"},
	        pipeline: [
	            { $match:
	                    { $expr:
	                            { $eq: ["$projectId", "$$projectId"] }
	                    }
	            },
	            {
	                $group: {
	                    _id: "$createdAt",
	                }
	            }
	        ],
	        as: "postDates"
	    }
	},
	{
	    $lookup: {
	        from: "snippets",
	        let: {"projectId": "$_id"},
	        pipeline: [
	            { $match:
	                    { $expr:
	                            { $eq: ["$projectId", "$$projectId"] }
	                    }
	            },
	            {
	                $group: {
	                    _id: "$createdAt",
	                }
	            }
	        ],
	        as: "snippetDates"
	    }
	},
]);
Lookups are a powerful way to make more efficient and complex requests in MongoDB. If you're working with MongoDB for your project, it's a must-know feature!
Using MongoDB $lookup for Graph-like/Join-like Requests

Comments (loading...)

Postulate
