Using $regex to filter documents by a referenced document field

Hello.

I have a slight problem and I would like to ask for a help :slight_smile:

I have a Expense model, it has a field category which is a reference to an Category document.

I want to be able to find all the documents that pass the regex test. I know i have a category.name = “Food” for example. So I would like to filter all the Expense documents that pass the test like this one:

	const result = await Expense.find({ 'description': { $regex: ".*test.*", $options: 'i' } }).populate<{
			category: CategoryType
		}>("category");

		console.log("Result: ", result)

But for some reason I do not get any results. I am using mongoose and Node.js + typescript. I suspect there is a problem with the populate, because when I query Expenses for a field of type string in the Expense collection, everything is fine and i get some results.

What can be the issue here? Thanks in advance :slight_smile:

Uh sorry, I gave the wrong example. This is the correct example:

const result = await Expense.find({ 'category.name': { $regex: ".*ood.*", $options: 'i' } }).populate<{
	category: CategoryType
}>("category");

console.log("Result: ", result)

My apologies :slight_smile:

Hello,

There is only a workaround for this case, as the order of query execution happens in the following order.

  1. Expense documents will be fetched first.
  2. Populate method will be invoked subsequent to that for each expense document.
  3. A merge happens as the final step.

A similar case can be found at stack-over-flow. The following text has been quoted from there. The workaround by the them has also been enclosed below.

Mongoose’s populate function doesn’t execute directly in Mongo. Instead after the initial find query returns a set a documents, populate will create an array of individual find queries on the referenced collection to execute and then merge the results back into the original documents. So essentially your find query is attempting to use a property of the referenced document (which hasn’t been fetched yet and therefore is undefined) to filter the original result set.

In this use case it seems more appropriate to store emails as a subdocument array rather than a separate collection to achieve what you want to do. Also, as a general document store design pattern this is one of the use cases that makes sense to store an array as a subdocument: limited size and very few modifications.

The workaround - Example:

Users.find().populate({
  path: 'email',
  match: {
    type: 'Gmail'
  }
}).exec(function(err, users) {
  users = users.filter(function(user) {
    return user.email; // return only users with email matching 'type: "Gmail"' query
  });
});

The original discussion:

Thanks
WeDoTheBest4You

1 Like