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)
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"
}
}
yeah i avoid dbref like the plague 🤪
Useful! You Save my time
Thank you
Very helpful. The explanation was spot on and brilliant. Thanks !