As part of some 'people you may know' function, we have an app that sends an array of the last 9 digits of the users contacts (last 9 digits as there are issues with the way users may store contact details like using a country code etc). The API must then perform a lookup where we need to find any user documents in our database where the last 9 digits of any users phone number matches a phone number in the array.
I was hoping to do something like:
usersContacts = [123456789, 987654321, 123443212,...]
find: {
phoneNumber: {
$in: usersContacts,
$regex: 'Some last 9 digit match regex'
}
}
The following does work in an aggregation pipeline:
$match: {
phoneNumStr: {
$in: [/861650975$/, /861650976$/]
}
}
Two issues with the above are that I needed project the phone number field to a string first (which is fine) but also would need to add N number of different regex statements by looping over the array sent by the app and creating /{phoneNum}$/
Are there any performance issues with this? Or is this okay?
CodePudding user response:
Query
- keeps
usersContacts
as numbers - converts only the phone in the database to number
- does the n comparisons using numbers (instead of strings and regex)
So the overal gain is no need to convert to string, and compare numbers instead of string, in case you have big array, it will be faster.
*assumes tha usersContacts
if phone is 001234567
will have only 1234567
the first zero digits will be missing. ($toInt
works this way also in databse so they will be equal)
aggregate(
[{"$match":
{"$expr":
{"$in":
[{"$toInt":
{"$substrCP":
["$phone", {"$subtract": [{"$strLenCP": "$phone"}, 9]}, 9]}},
[123456789, 12345678]]}}}])