Posted on Feb 23, 2020

MongoDB: Using aggregation pipeline to extract DBref using $lookup operator

Problem Statement:

While using aggregation pipeline on collection having DBRef to other collection, using $lookup does not resolve to value due to $id field in the reference.

Imagine we have a collection called posts with the following structure.

{ "_id": ObjectId("5126bbf64aed4daf9e2ab771"), "title": "Once in a Lifetime", "content": "Morbi quis tortor id nulla ultrices aliquet.", "author": { "$ref": "users", "$id": ObjectId("5126bc054aed4daf9e2ab772") } }

The DBRef in this example points to a document in the users collection that has ObjectId("5126bc054aed4daf9e2ab772") in its _id field.

To resolve,author field we need to use $lookup stage, which has syntax as given below but will not work, due to the presence of $ in the field name.

db.posts.aggregate([ { $lookup: { from: "users", localField: "author['$id']", // or author.$id foreignField: "_id", as: "author" } } ])

Solution:

One of the workaround I found to resolve the DBRefs with $lookup operator, was with using $objectToArray, $arrayElemAt and $addFields operator.

Idea behind this is to somehow, extract the $id value in author field and then use $lookup operator to get the desired result.

db.posts.aggregate([ { $addFields: { "author": { $arrayElemAt: [{ $objectToArray: "author" }, 1] } } }, { $addFields: { "author": "author.v" } }, { $lookup: { from: "users", localField: "author", foreignField: "_id", as: "author" } }, { $addFields: { "author": { $arrayElemAt: ["$author", 0] } } } ])

will return result:

[ { "_id": ObjectId("5126bbf64aed4daf9e2ab771"), "title": "Once in a Lifetime", "content": "Morbi quis tortor id nulla ultrices aliquet.", "author": { "_id": ObjectId("5126bc054aed4daf9e2ab772"), "name": "Uta Charman", "avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1", "aboutText": "Vivamus tortor." } } ]

Explanation:

Stage 1:

{ $addFields: { "author": { $arrayElemAt: [{ $objectToArray: "author" }, 1] } } }

Converts author object from DBref reference to array of key value pairs, and extracts key-value pair of $id in author field

{ ... author: { k: $id, v: ObjectId("5126bc054aed4daf9e2ab772") } }

Stage 2:

 { $addFields: { "author": "author.v" } }

Maps value of $id key to author field.

{ ... author: ObjectId("5126bc054aed4daf9e2ab772") }

Stage 3:

 { $lookup: { from: "users", localField: "author", foreignField: "_id", as: "author" } }

Using $lookup operator to fetch for local field author from users collection

{ ... author: [ { "_id": ObjectId("5126bc054aed4daf9e2ab772"), "name": "Uta Charman", "avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1", "aboutText": "Vivamus tortor." } ] }

Stage 4:

{ $addFields: { "author": { $arrayElemAt: ["$author", 0] } } }

Maps author array type field to object field.

{ ... author: { "_id": ObjectId("5126bc054aed4daf9e2ab772"), "name": "Uta Charman", "avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1", "aboutText": "Vivamus tortor." } }

Note:

As per Mongo DB's Documentation Database References — MongoDB Manual

Unless you have a compelling reason to use DBRefs, use manual references instead.

Top comments (4)

Faizaan Shaikh • Sep 24 '20

Very helpful article!

There is a missing $ from the snippet below. It took me hours to figure it out.
{
$addFields: {
"author": {
$arrayElemAt: [{ $objectToArray: "$author" }, 1]
}
}
},
{
$addFields: {
"author": "$author.v"
}
}