Filter after lookup

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?

1 Like

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

2 Likes

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()

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.

@steevej

Hey, here are the indexes:

db["RepositoryInfo"].createIndexes([ { "_id" : 1 },{ "ownerId" : 1 },{ "name" : 1 },{ "cloudId" : 1 } ]);

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.

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.