MongoDB aggregation gets exponentially complicated by the minute!
I am in so far as to $unwind
a nested array and then perform a $lookup
by the _id
of each object from the unwinded nested array. My final attempt is to reverse the unwinding with $group
. However, I am unable to reconstruct the original embedded array, with its original property name, along with the rest of the original immediate properties of each document.
Here is my attempt so far:
db.users.aggregate([
{
$unwind: "$profile",
$unwind: {
path: "$profile.universities",
preserveNullAndEmptyArrays: true
}
},
{
$lookup: {
from: "universities",
localField: "profile.universities._id",
foreignField: "_id",
as: "profile.universities"
}
},
{
$group: {
_id: "$_id",
emails: { "$first": "$emails" },
profile: { "$first": "$profile" },
universities: { "$push": "$profile.universities" }
}
}
]).pretty()
What I get is something like this:
{
"_id" : "A_USER_ID",
"emails" : [
{
"address" : "AN_EMAIL_ADDRESS",
"verified" : false
}
],
"profile" : {
"name" : "NAME",
"company" : "A COMPANY",
"title" : "A TITLE",
"phone" : "123-123-1234",
"disabled" : false,
"universities" : [
{
"_id" : "ID_1",
"name" : "UNIVERSITY_NAME_1",
"code" : "CODE_1",
"styles" : {AN_OBJECT}
}
]
},
"universities" : [
[
{
"_id" : "ID_1",
"name" : "UNIVERSITY_NAME_1",
"code" : "CODE_1",
"styles" : {AN_OBJECT}
}
],
[
{
"_id" : "ID_2",
"name" : "UNIVERSITY_NAME_2",
"code" : "CODE_2",
"styles" : {AN_OBJECT}
}
]
]
}
There are 2 issues with this result:
universities
is an array of arrays of one object each, since the $lookup
returned a single element array for the original $profile.universities
nested array. It should be just an array of objects. universities
should take its original place as nested under profiles
. I am aware why the original profile.universities
is the way it is, because I am using the $first
operator. My intent behind this is to retain all of the original properties of profile
, in junction with retaining the original nested universities
array.Ultimately, what I need is something like this:
{
"_id" : "A_USER_ID",
"emails" : [
{
"address" : "AN_EMAIL_ADDRESS",
"verified" : false
}
],
"profile" : {
"name" : "NAME",
"company" : "A COMPANY",
"title" : "A TITLE",
"phone" : "123-123-1234",
"disabled" : false,
"universities" : [
{
"_id" : "ID_1",
"name" : "UNIVERSITY_NAME_1",
"code" : "CODE_1",
"styles" : {AN_OBJECT}
},
{
"_id" : "ID_2",
"name" : "UNIVERSITY_NAME_2",
"code" : "CODE_2",
"styles" : {AN_OBJECT}
}
]
}
}
Is there another operator that I can use instead of $group
to achieve this? Or am I understanding the purpose of $group
incorrectly?
Edit: This is the original post, for context: If Mongo $lookup is a left outer join, then how come it excludes non-matching documents?
MongoDB provides a variety of state operators. The $unwind operator is one of those operators. The $unwind operator is used to deconstructing an array field in a document and create separate output documents for each item in the array.
MongoDB $unwind transforms complex documents into simpler documents, which increase readability and understanding. This also allows us to perform additional operations, like grouping and sorting on the resulting output.
This means $first returns the first order type for the documents between the beginning of the partition and the current document.
$match takes a document that specifies the query conditions. The query syntax is identical to the read operation query syntax; i.e. $match does not accept raw aggregation expressions. Instead, use a $expr query expression to include aggregation expression in $match .
Because the $lookup
operator produces an array field, you need to $unwind
the new field before the $group
pipeline to get the desired result:
db.users.aggregate([
{ "$unwind": "$profile" },
{ "$unwind": {
"path": "$profile.universities",
"preserveNullAndEmptyArrays": true
} },
{ "$lookup": {
"from": "universities",
"localField": "profile.universities._id",
"foreignField": "_id",
"as": "universities"
} },
{ "$unwind": "$universities" },
{ "$group": {
"_id": "$_id",
"emails": { "$first": "$emails" },
"profile": { "$first": "$profile" },
"universities": { "$push": "$universities" }
} },
{ "$project": {
"emails": 1,
"profile.name" : 1,
"profile.company": 1,
"profile.title" : 1,
"profile.phone" : 1,
"profile.disabled": 1,
"profile.universities": "$universities"
} }
]).pretty()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With