jeff ayan jeff ayan - 2 months ago 34
Node.js Question

Could not find the matching Number in mongodb aggregation using Regex

I have a collection

//demo name : user_informations

{
"_id" : 3015,
"Phone Number" : "9159571195",
"First Name" : "logesh",
"Last Name" : "chandiran",
"Email ID" : "logu@gmail.com",
"Gender" : "male",
"customerID" : "CUST3015",
"createddate" : 1472482064363.0,
"modifieddate" : 1474384997049.0,
"transationHistory" : [
{
"transactionStatus" : "Success",
"transactionAmount" : 2500,
"paymentId" : "Q8urVX9Cpf",
"transactionDate" : 1472754600000.0,
"transactionId" : "7egsOpmqBR",
"comments" : "EMI",
"medium" : "Cash"]
}
}


i'm using the following query

db.user_informations.aggregate([
{
$match:{"Phone Number": '9159571195'}},
{$unwind:'$transationHistory'},
{"$match":{"$or":[
{"transationHistory.transactionAmount":{$regex:/2500/i}},
{"transationHistory.transactionId":{$regex:/2500/i}},
{"transationHistory.paymentId":{$regex:/2500/i}},
{"transationHistory.transactionStatus":{$regex:/2500/i}},
{"transationHistory.medium":{$regex:/2500/i}},
{"transationHistory.comments":{$regex:/2500/i}}
]}},
{"$group":{_id: null,"count":{$sum: 1 },"result":{$push:"$transationHistory"}}}

])


The above query works fine with values in string but not with numbers it is returning as null. How to match the value with numbers in collection fields.
I need the count and result of matching data from the collection.

Answer

Regex is a tool for strings, not numbers. Use regular number comparison operators, like $eq, $gt, $lt, etc.

Basically, it'll be:

db.user_informations.aggregate(
    {$match:{"Phone Number": '9159571195'}},
    {$unwind:'$transationHistory'},
    {"$match":{
        "$or":[
            {"transationHistory.transactionAmount":{$eq:2500}}
        ]
    },
    {"$group":{
        _id: null,
        "count":{$sum: 1 },
        "result":{$push:"$transationHistory"}
    }})

Hopefully, all brackets are in place.

Comments