I've searched the internet and StackOverflow, but I cannot find the answer or even the question.
I have two collections, reports
and users
. I want my query to return all reports and indicate if the specified user has that report as a favorite in their array.
Reports Collection
{ _id: 1, name:"Report One"}
{ _id: 2, name:"Report Two"}
{ _id: 3, name:"Report Three"}
Users Collection
{_id: 1, name:"Mike", favorites: [1,3]}
{_id: 2, name:"Tim", favorites: [2,3]}
Desired Result for users.name="Mike"
{ _id: 1, name:"Report One", favorite: true}
{ _id: 2, name:"Report Two", favorite: false}
{ _id: 3, name:"Report Three", favorite: true}
All of the answers I can find use $unwind on the local (reports
) field, but in this case the local field isn't an array. The foreign field is the array.
How can I unwind the foreign field? Is there a better way to do this?
I saw online that someone suggested making another collection favorites
that would contain:
{ _id: 1, userId: 1, reportId: 1 }
{ _id: 2, userId: 1, reportId: 3 }
{ _id: 3, userId: 2, reportId: 2 }
{ _id: 4, userId: 2, reportId: 3 }
This method seems like it should be unnessesary. It should be simple to join onto an ID in a foreign array, right?
Perform $lookup with Equality Match: foreignField: It is any field of from collection the value of which is to be compared which the localField value. as: It is the array field in which the matched documents of from collection are to be stored.
The $project takes a document that can specify the inclusion of fields, the suppression of the _id field, the addition of new fields, and the resetting of the values of existing fields. Alternatively, you may specify the exclusion of fields. Specifies the inclusion of a field.
$lookup operations join data from two collections in the same database based on a specified field. $lookup operations can be useful when your data is structured similarly to a relational database and you need to model large hierarchical datasets.
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 ...
You can use $lookup with custom pipeline which will give you 0
or 1
result and then use $size to convert an array to single boolean value:
db.reports.aggregate([
{
$lookup: {
from: "users",
let: { report_id: "$_id" },
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: [ "$name", "Mike" ] },
{ $in: [ "$$report_id", "$favorites" ] }
]
}
}
}
],
as: "users"
}
},
{
$project: {
_id: 1,
name: 1,
favorite: { $eq: [ { $size: "$users" }, 1 ] }
}
}
])
Alternatively if you need to use MongoDB version lower than 3.6 you can use regular $lookup
and then use $filter to get only those users where name
is Mike
:
db.reports.aggregate([
{
$lookup: {
from: "users",
localField: "_id",
foreignField: "favorites",
as: "users"
}
},
{
$project: {
_id: 1,
name: 1,
favorite: { $eq: [ { $size: { $filter: { input: "$users", as: "u", cond: { $eq: [ "$$u.name", "Mike" ] } } } }, 1 ] }
}
}
])
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