So as you all know, find()
returns an array of results, with findOne()
returning just a simply object.
With Angular, this makes a huge difference. Instead of going {{myresult[0].name}}
, I can simply just write {{myresult.name}}
.
I have found that the $lookup
method in the aggregate pipeline returns an array of results instead of just a single object.
For example, I have two colletions:
users
collection:
[{
"firstName": "John",
"lastName": "Smith",
"country": 123
}, {
"firstName": "Luke",
"lastName": "Jones",
"country": 321
}]
countries
collection:
[{
"name": "Australia",
"code": "AU",
"_id": 123
}, {
"name": "New Zealand",
"code": "NZ",
"_id": 321
}]
My aggregate $lookup
:
db.users.aggregate([{
$project: {
"fullName": {
$concat: ["$firstName", " ", "$lastName"]
},
"country": "$country"
}
}, {
$lookup: {
from: "countries",
localField: "country",
foreignField: "_id",
as: "country"
}
}])
The results from the query:
[{
"fullName": "John Smith",
"country": [{
"name": "Australia",
"code": "AU",
"_id": 123
}]
}, {
"fullName": "Luke Jones",
"country": [{
"name": "New Zealand",
"code": "NZ",
"_id": 321
}]
}]
As you can see by the above results, each country
is an array instead of a single object like "country": {....}
.
How can I have my $lookup
return a single object instead of an array since it will only ever match a single document?
With the latest version of mongoose (mongoose >= 3.6), you can but it requires a second query, and using populate differently. After your aggregation, do this: Patients. populate(result, {path: "patient"}, callback);
The MongoDB Lookup operator, by definition, “Performs a left outer join to an unshared collection in the same database to filter in documents from the “joined” collection for processing.” Simply put, using the MongoDB Lookup operator makes it possible to merge data from the document you are running a query on and the ...
For performing MongoDB Join two collections, you must use the $lookup operator. It is defined as a stage that executes a left outer join with another collection and aids in filtering data from joined documents. For example, if a user requires all grades from all students, then the below query can be written: Students.
Starting in MongoDB 5.0, the $eq , $lt , $lte , $gt , and $gte comparison operators placed in an $expr operator can use an index on the from collection referenced in a $lookup stage. Limitations: Multikey indexes are not used.
You're almost there, you need to add another $project
stage to your pipeline and use the $arrayElemAt
to return the single element in the array.
db.users.aggregate(
[
{ "$project": {
"fullName": {
"$concat": [ "$firstName", " ", "$lastName"]
},
"country": "$country"
}},
{ "$lookup": {
"from": "countries",
"localField": "country",
"foreignField": "_id",
"as": "countryInfo"
}},
{ "$project": {
"fullName": 1,
"country": 1,
"countryInfo": { "$arrayElemAt": [ "$countryInfo", 0 ] }
}}
]
)
You can also use "preserveNullAndEmptyArrays"
Like so:
db.users.aggregate(
[
{ "$project": {
"fullName": {
"$concat": [ "$firstName", " ", "$lastName"]
},
"country": "$country"
}},
{ "$lookup": {
"from": "countries",
"localField": "country",
"foreignField": "_id",
"as": "countryInfo"
}},
{"$unwind": {
"path": "$countryInfo",
"preserveNullAndEmptyArrays": true
}
},
]
)
db.users.aggregate([
{
$lookup: {
from: 'countries',
localField: 'country',
foreignField: '_id',
as: 'country'
}
},
{
$unwind: '$country'
}
]).pretty()
You can use this mongo query for getting the country object
When you don't want to repeat all fields in project, just overwrite the field in question with $addFields:
db.users.aggregate([
{ "$project": {
"fullName": {
"$concat": [ "$firstName", " ", "$lastName"]
},
"country": "$country"
}},
{ "$lookup": {
"from": "countries",
"localField": "country",
"foreignField": "_id",
"as": "countryInfo"
}},
{ "$addFields": {
"countryInfo": {
"$arrayElemAt": [ "$countryInfo", 0 ]
}
}}
])
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