Querying a number field with pattern search

Hello
I have a collection and in it there is a field with number datatype. I am using Spring Mongo repository class and need to get all the records where the number field contains a specific number. For example, give me all the records where 2 is present in the number field. This will give me all the records with field values - 1234,2345,3421 etc… but not 1345.
I used @Query and $where clause but getting an error “Query failed with error code 2 and error message ‘$where cannot be applied to a field’”.

Hi @ragarwal1_N_A,

Welcome to MongoDB community.

I would say that using $where is not recommended and should be avoided.

The optimal way to attack this search issue is by:

  1. You should be using Atlas as your hosting (very recommended) and use Atlas search with the regex clause on this field whithin $search stage.
    https://docs.atlas.mongodb.com/reference/atlas-search/regex/

  2. If you cannot use atlas, you should change your data model to host a field which has all digits in an array : [1, 2,3 …] . Index that field and search it using standard filter in a find query.

If none of the above is possible I would suggest using a $regex in a standard filter
Thanks
Pavel

Hello
Thanks for your answer. However the regex approach was tried first and it did not work. I am giving more details

  1. data looks like the below

{“empId”:1, “empName”:“Tom”} {“empId”:10, “empName”:“Peter”}
the empId field is of type number.

public interface EmployeeRepository extends MongoRepository<Employee,String> {
@Query(" {$or :[{‘empName’: {‘$regex’:$0}}, {‘empId’:{‘$regex’:$0}}]}")
List findAllSearch(String pattern);
}

the above works fine for empName but not for empId. For example if I want to find all employees which has a 1 in it.
As an alternative I tried

> public interface EmployeeRepository extends MongoRepository<Employee,String> {
> @Query(" {$or :[{'empName': {'$regex':$0}}, {'empId':{'$eq':$0}}]}")
> List<Employee> findAllSearch(String pattern);
> }

or

> public interface EmployeeRepository extends MongoRepository<Employee,String> {
> @Query(" {$or :[{'empName': {'$regex':$0}}, {'empId':$0}]}")
> List<Employee> findAllSearch(String pattern);
> }
but none of it works.  I have a GUI application where the user can specify pattern search on any of the display column (taking the above example it can be name or id) and hence I am wondering if writing a generic method is possible since the datatype for empName (String) and empId (number) is different.

HI @ragarwal1_N_A,

You are correct the $regex is operating on strings.

You can do a non-optimal workaround using aggregation $addFields and adding a string variant:

 db.employee.aggregate([{ $addFields : {
  empIdStr: {
    $toString: '$empId'
  }
},{ $match {
  empIdStr : /2/
}
}, { $project :  {empIdStr : 0} }
]

This will allow you to search the needed documents but it will do a collection scan and connot use an index.

What you might do is to add the number as idArr from now on. If you wish to morph the documents to have it you can use the following Aggregation pipeline in an update:

db.employee.updateMany({},[{$addFields: {
  empIdStr: { $toString : "$empId"}
}}, {$addFields: {
  idArr:  {$map : {
          input : {$range : [ 1, {$add : [{ $strLenBytes : "$empIdStr" },1]}]}, 
          in : {$toDouble : { $substr : ["$empIdStr",  {$subtract : ["$$this",1]}, 1] }}
          }
  }
}}]);

Now you can index idArr : 1 and search easily:

db.employee.find({idArr : 2 });

Best regards,
Pavel

Thanks for the details. I have removed the @Query and used my custom class implementation with the repository class. I used Criteria class along with Query (and orCondition on the fields) for this task.