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"
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] }
"_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."
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")
"_id": ObjectId("5126bc054aed4daf9e2ab772"),
"name": "Uta Charman",
"avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1",
"aboutText": "Vivamus tortor."
author: {
"_id": ObjectId("5126bc054aed4daf9e2ab772"),
"name": "Uta Charman",
"avatar": "https://robohash.org/porroasperioreshic.png?size=50x50&set=set1",
"aboutText": "Vivamus tortor."
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"
Full-stack developer.
Strive to develop efficient backend solutions and design beautiful & UX friendly frontend.
Enjoys the music of keyboard, avid podcast listener and a horror movie buff.
Built on Forem — the open source software that powers DEV and other inclusive communities.
Made with love and Ruby on Rails. DEV Community © 2016 - 2024.
