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.

Comments

[Faizaan Shaikh]

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"
}
}

[Dĵ ΝιΓΞΗΛψΚ]

yeah i avoid dbref like the plague 🤪

[Shayan]

Useful! You Save my time
Thank you

[Sriram Sridharan]

Very helpful. The explanation was spot on and brilliant. Thanks !