CODEWITHSUNDEEP
javascriptregexnode.jsmongodbmongoose
Alnoor Khan
Alnoor Khan

Reputation: 53

Number Search with (^)carat in mongoDB does'nt work

unable to search in my mongoDB collection with data-type is Number and carat doesnt seem to work for me Search query

  db.collection.find({mobile : /^9/});

DB collection 

{
    "_id" : ObjectId(),
    "Name" : "Mr.XXX",
    "mobile" : NumberLong(9876543210),
    "date" : ISODate("2015-07-09T07:21:45.552Z"),
    "__v" : 0
}

/* 1 */
{
    "_id" : ObjectId(),
    "Name" : "Mr.YYY",
    "mobile" : NumberLong(887654210),
    "__v" : 0
}

But the search result doesnt seem to work for mobile and it works for other dataTypes.

Upvotes: 0

Views: 422

Answers (3)

Sammaye
Sammaye

Reputation: 43884

You cannot perform a regex on a number value however, you should change your schema.

Mobile numbers regularly start with a 0 in many countries (UK for example) and do not follow the rules for a strict integer (which NumberLong is) nor are they are a strict size normally as such you should not be storing them as a number type but instead a string type, at which point you can regex on them.

As such, instead of trying to find some weird work around using aggregation or $where, both of which will result in a painful death you should instead change your schema to match the information you are actually entering.

Upvotes: 1

Neo-coder
Neo-coder

Reputation: 7840

As per mongoDB $regex it said that regex regular expression capabilities for pattern matching strings in queries. So first you should convert mobile to String using $substr, assuming your mobile number always 10 digit number.

This aggregation may slow but it satisfied your criteria, check below aggregation query :

db.collectionName.aggregate([
{
    "$project": {
        "mobileToString": {
            "$substr": [
                "$mobile",
                0,
                10
            ]
        },
        "Name": 1,
        "mobile": 1,
        "date": 1,
        "__v": 1
    }
},
{
    "$match": {
        "mobileToString": /^9/
    }
},
{
    "$project": {
        "Name": 1,
        "mobile": 1,
        "date": 1,
        "__v": 1
    }
}
]).pretty()

Upvotes: 0

Blakes Seven
Blakes Seven

Reputation: 50416

The data is "numeric" so a Regular expression does not work here, as they only work on "strings".

You can use JavaScript evaluation of $where for this, since JavaScript can cast the "type":

db.collection.find(function() { return this.mobile.toString().match(/^9/) })

But that isn't a great idea since $where cannot use an index to match and relys on the coded condition to compare.

If you need to do this sort of matching then your "numeric" data needs to be a "string", or at least have something in the document with the "string" representation:

{
    "_id" : ObjectId(),
    "Name" : "Mr.XXX",
    "mobile" : "9876543210",
    "date" : ISODate("2015-07-09T07:21:45.552Z"),
    "__v" : 0
}

Then your $regex query works as expected when matching against a "string".

db.collection.find({ "mobile": /^9/ })

Upvotes: 0

Related Questions