3

Im trying to do a query in mongodb similar to SQL example:

SELECT * from example where contactphone like '4832%';

My first idea was to do something like:

db.example.find({ contactphone: /^4832/ })

but it doesnt return what I am looking for, as contactphone is a number.

Is there any good practices-way to do such operations in mongodb on numbers?

EDIT: phone number may be a bad example. Lets say i need to find all numbers that CONTAIN number digit 9. How can i achive that?

Jarema
  • 3,291
  • 2
  • 17
  • 30
  • 2
    If you need `LIKE` operations, I suggest storing phone numbers as strings. That would also allow for international numbers with a leading zero such as `011468...`. That said, if you have a fixed number of digits, you could just as well use `$gt` and `$lt`. – Joachim Isaksson Jun 14 '14 at 17:25
  • 1
    When you have a number on which arithmetics are pointless - like a zip-code, social security number or telephone number - you should always store it as a string, not as an integer. – Philipp Jun 14 '14 at 17:27

1 Answers1

8

Do not store telephone numbers as integers. Store them as strings. When you have a value which consists only of digits and on which arithmetics are pointless, like a zip-code, a bank account number or a telephone number, then storing it as a number does not make much sense.

  • As you already noticed, numbers are almost impossible to search when you want to search for a digit-sequence, because computer systems store integers in binary, not in decimal.
  • You can not have leading zeros
  • When the number is too long, you will either encounter an integer overflow or you will have it converted to a floating-point number (at the discretion of your MongoDB driver). Both will result in very strange behavior.

All of this applies to almost any database, by the way, not just MongoDB.

But should you be absolutely determined to keep them as numbers, here are two things you can do.

  1. When the number of unknown digits is fixed, you could use the $gt and $lt operators to search for a range. contactphone:{$gt:5556000, $lt:5556999} would find all numbers with the pattern 5556xxx.
  2. You could use a $where-query which uses a javascript function to internally convert every number to a string and then applies your regular expression to that string. $where: "String(this.contactphone).match(/^4832/) != null". I hope you don't have many documents in your database, because when you do, this query could take a while.
Philipp
  • 67,764
  • 9
  • 118
  • 153
  • Of course, You are right. I do that all the time in psql - store lot of "numbers" as string due to the same reasons as You mentioned. It's not any production enviroment. I m just experimenting BEFORE actually using mongo anywhere. Situation appeared when i did mongoimport on some CDR, and mongoimport automatically set for those phonenumbers type: numberlong. I know its not that question, but do You know way to choose a type for a specific field when mongoimporting? – Jarema Jun 14 '14 at 18:05
  • @Jarema Please ask this as a new question. – Philipp Jun 14 '14 at 18:06
  • yep, probably a better idea. question link (if You're interested:) ): http://stackoverflow.com/questions/24223443/mongoimport-choosing-field-type – Jarema Jun 14 '14 at 19:22