Using $regex to filter documents by a referenced document field

Gilga_Mesh · 2024-04-11T11:14:41.533Z

Hello.

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

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

Gilga_Mesh · 2024-04-11T12:13:01.969Z

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

wedothebest_We_do_the_Best · 2024-04-12T12:01:11.972Z

Hello,

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

Expense documents will be fetched first.
Populate method will be invoked subsequent to that for each expense document.
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:

stackoverflow.com

Find after populate mongoose

javascript, node.js, mongodb, mongoose

asked by monokh on 01:37PM - 11 Jul 15 UTC

Thanks
WeDoTheBest4You

Yakeen_Sabha · 2025-01-09T12:10:53.979Z

Hi, did u solve it ? cuz I have the same problem