Filter after lookup

Working with Data
,
#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?

1 Like
#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

2 Likes
#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()

#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.

#5

@steevej

Hey, here are the indexes:

db["RepositoryInfo"].createIndexes([ { "_id" : 1 },{ "ownerId" : 1 },{ "name" : 1 },{ "cloudId" : 1 } ]);
#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.

#7

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