MongoDB: Using aggregation pipeline to extract DBref using $lookup operator
Tags: mongodb, lookup, dbref, aggregation
Published: 2020-02-23 23:55:49

## Problem Statement:

While using aggregation pipeline on collection having [DBRef](https://docs.mongodb.com/manual/reference/database-references/#dbrefs) to other collection, using [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#pipe._S_lookup) does not resolve to value due to `$id` field in the reference. 

Imagine we have a collection called `posts` with the following structure.
```js
{
    "_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.

```js
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.

```js
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:
```js
[
    {
        "_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:**
```js
{
    $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
```js
{
  ...
  author: {
    k: $id,
    v: ObjectId("5126bc054aed4daf9e2ab772")
  }
}
```

**Stage 2:**
```js
 {
    $addFields: {
        "author": "author.v"
    }
}
```
Maps value of `$id` key to `author` field.
```js
{
  ...
  author: ObjectId("5126bc054aed4daf9e2ab772")
}
```

**Stage 3:**
```js
 {
    $lookup: {
        from: "users",
        localField: "author",
        foreignField: "_id",
        as: "author"
    }
}
```
Using `$lookup` operator to fetch for local field `author` from `users` collection
```js
{
  ...
  author: [
      {
          "_id": ObjectId("5126bc054aed4daf9e2ab772"),
          "name": "Uta Charman",
          "avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1",
          "aboutText": "Vivamus tortor."
      }
  ]
}
```

**Stage 4:**
```js
{
    $addFields: {
        "author": { $arrayElemAt: ["$author", 0] }
    }
}
```
Maps `author` array type field to object field.
```js
{
  ...
  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](https://docs.mongodb.com/manual/reference/database-references/#dbrefs)

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