Performance improvoment in a $addfields query mongodb

Working with Data
,
1

I’m doing a performance improvement in a MongoDB query, the query search for a value in a String field and a numeric field.

The actual query uses $addField to search in numeric fields like a text field, with this we can use regex to do something like this:

The user types ‘123’

Search for not only the exact match, but if the data contains ‘123’ like these examples:

{
 name: 123 Example
 usedId: 9595
}
{
 name: Example
 usedId: 1123
}

Both registers will be returned because one of them has ‘123’ on name fields and the other one contains ‘123’ in userId.

But, $addFields doesn’t use indexes, and this impacts the query’s performance.

There is a way to use $addFields with indexes, or to make regex with numbers, or something like that to improve performance?

My solution until now is this query that uses indexes is:

[
  {
  $match:
      {
        $or: [
                    {
            name:
                    {
                      $regex: "123", $options: "i"
                    }
            },
          {
              userId :
            {
                // idk what command I should use here
            }
          }
            ]
      }
  }
]

The actual aggregation pipeline is:

   [
   	{ 
   		$addFields: 
   		{ 
   			userId: 
   			{
   				$toString: '$userId'
   			}	
   		} 
   	},
   	{ 
   		$match: 
   			{ 
   				$or: 
   					[ 
   						{
   							name: 
   								{
   									$regex: '?0', 
   									$options:'i'
   								}
   						}, 
   						{
   							userId: {
   									$regex: '?0'
   								}
   						}, 
   					] 
   			} 
   	},
   	{ 
   		$group: 
   			{ 
   				_id: 
   					{ 
   						userId: "$userId", 
   						name: "$name", 
   					} 
   			} 
   	},
   	{ 
   		$project: 
   			{ 
   			_id: 0, userId: "$_id.userId", 
   			name: "$_id.name" 
   			} 
   	},
   	{ 
   		$sort : { 
   			name : 1 
   			} 
   	},
   	{ 
   		$facet: { 
   			metadata: [ 
   				{ 
   					$count: "total" 
   				} 
   			], 
   			data: [ 
   				{ 
   					$skip: '?1' 
   				}, 
   				{ 
   					$limit: '?2' 
   				} 
   			] 
   		} 
   	}
   ]
2

Not really. Any $match that works on computed values, such as $addFields, cannot use indexes. Indexes are for stored values.

3

@steevej , should I create a new String column to search the values? Or have something that I can use to make this ‘like’ function

4

If it is a frequent query, it might be worth while to do it. But note that $regex match is still not a stellar performer when the regex is not anchor.

You might try to experiment with something I am not too familiar with

I really do not know if you can use $regex with text indexes.