J_C1
(J C)
1
I have 2 collections: Resources and Repo. Resources has a RepoId property.
I want to find all documents in Resources that have a RepoId that doesn’t exist in the Repo collection.
I have used the following query in MongDB Compass CLI (3.4.10 Community):
db.resources.aggregate(
[
{
$lookup: {
from: "RepositoryInfo",
localField: "repoId",
foreignField: "_id",
as: "repo",
},
},
]
);
This returns a repo field that is either an empty array, or an array with 1 object (the Repo document).
How can I filter this aggregated data so only results with an empty array are returned?
2 Likes
NeNaD
(Nenad Milosavljevic)
2
Hi,
You can just add one more Aggregation stage that will match all documents that have empty repo
array. You can do it like this:
db.resources.aggregate([
{
"$lookup": {
"from": "RepositoryInfo",
"localField": "repoId",
"foreignField": "_id",
"as": "repo"
}
},
{
"$match": {
"repo": []
}
}
])
Working example
3 Likes
J_C1
(J C)
3
Thanks @NeNaD, that works as expected.
It is very slow though. Takes 7 minutes on a collection with 9 million documents.
Is there a different approach I should take if I want a more performant query? e.g. something instead of aggregate()
steevej
(Steeve Juneau)
4
What indexes do you have on the collection RepositoryInfo?
That might be the best you can do depending on the configuration of your system and the size of your collections.
J_C1
(J C)
5
@steevej
Hey, here are the indexes:
db["RepositoryInfo"].createIndexes([ { "_id" : 1 },{ "ownerId" : 1 },{ "name" : 1 },{ "cloudId" : 1 } ]);
steevej
(Steeve Juneau)
6
Size in terms of Mb.
Anything else running on this server?
How many documents are returned in the result set?
The explain plan is useful to investigate performance issues.
system
(system)
Closed
7
This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.