Filter array elements with $regex

Question

 ///sample Data 
{
    "_id" : "CUST1234",
    "Phone Number" : "9585290750",
    "First Name" : "jeff",
    "Last Name" : "ayan",
    "Email ID" : "",
    "createddate" : 1462559400000.0,
    "services" : [ 
        {
            "type" : "Enquiry",
            "timeSpent" : "0:00",
            "trxID" : "TRXE20160881",
            "CustomerQuery" : "Enquiry about travell agent numbers in basaveshwara nagara",
            "ServiceProvided" : "provided info through whatsapp",
            "Category" : "Tours/Travels",
            "callTime" : "2016-05-06T18:30:00.000Z",
            "ActualAmount" : 0,
            "FinalAmount" : 0,
            "DiscountRuppes" : 0,
            "DiscountPerctange" : 0
        }, 
        {
            "type" : "Enquiry",
            "timeSpent" : "0:00",
            "trxID" : "TRXE20160882",
            "CustomerQuery" : "Enquiry about Electric bill payment of house",
            "ServiceProvided" : "Service provided",
            "Category" : "Utility Services",
            "callTime" : "2016-05-10T18:30:00.000Z",
            "ActualAmount" : 0,
            "FinalAmount" : 0,
            "DiscountRuppes" : 0,
            "DiscountPerctange" : 0
        }, 
        {
            "type" : "Enquiry",
            "timeSpent" : "0:00",
            "trxID" : "TRXE20160883",
            "CustomerQuery" : "Enquiry about KPSC office number",
            "ServiceProvided" : "provided info through whatsapp",
            "Category" : "Govt Offices/Enquiries",
            "callTime" : "2016-05-13T18:30:00.000Z",
            "ActualAmount" : 0,
            "FinalAmount" : 0,
            "DiscountRuppes" : 0,
            "DiscountPerctange" : 0
        }, 
        {
            "type" : "Enquiry",
            "timeSpent" : "0:00",
            "trxID" : "TRXE20160884",
            "CustomerQuery" : "Enquiry about Sagara appolo hospital contact number",
            "ServiceProvided" : "provided the information through call",
            "Category" : "Hospitals/Equipments",
            "callTime" : "2016-05-14T18:30:00.000Z",
            "ActualAmount" : 0,
            "FinalAmount" : 0,
            "DiscountRuppes" : 0,
            "DiscountPerctange" : 0
        },
 ]
}

Expected Output : entire data that matches particular string in search box from "services" field.

     db.collection.aggregate([
        { 
            $match: { 
                "Phone Number": "9585290750", 
                "services": { $regex: "/^t/", $options: "s i" }
            }
        },                     
        {
            $project: {
                "Services": "services" 
            }
        }
    ]);

I am facing an issue in regex portion in the above Collection, services is an array field. Please help me to filter the data.

stackoverflow.com/questions/16252208/… this might help you — Panky031
– Panky031, Commented Sep 26, 2016 at 20:42
What is the embedded document field you are trying to match with your regex? — Sede
– Sede, Commented Sep 27, 2016 at 7:12
What is search box in your document? You can't apply a regular expression to an array what is the field's name in the subdocument your want to apply the regex to? — Sede
– Sede, Commented Sep 27, 2016 at 11:59
i just want to match the data with all fields in services and display the output in table — jeff ayan
– jeff ayan, Commented Sep 27, 2016 at 15:36

jeff ayan · Accepted Answer · 2016-09-29 10:15:31Z

Guys since i am new to Mongodb it took me a day to find a proper solution to my task. I have a solution to my issue. If you guys have better query than this, just post it or modify it....

 db.collections.aggregate([
        {"$match":{"Corporate_ID":"id"}},
        {"$unwind":"$services"},
        {"$match":{"$or":[
            {"services.type":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.timeSpent":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.trxID":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.CustomerQuery":{$regex:'F',"$options": "i"}},
            {"services.ServiceProvided":{$regex:'F',"$options": "i"}},
            {"services.Category":{$regex:'F',"$options": "i"}},
            {"services.callTime":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.ActualAmount":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.FinalAmount":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.DiscountRuppes":{$regex:'TRXF2016088142',"$options": "i"}},
            {"services.DiscountPerctange":{$regex:'TRXF2016088142',"$options": "i"}}                     
            ]}},
        {"$unwind":"$services"},
        {"$project":{
            "service":"$services"}
               }        
])

Sede · Accepted Answer · 2016-09-26 16:19:36Z

0

This is because you are passing in a string of JavaScript regular expression object to $regex. Change your regex to one of the following.

"service": { "$regex": /^t/, "$options": "si" }

or

"service": { "$regex": "^t", "$options": "si" }

answered Sep 26, 2016 at 16:19

Sede

61.5k20 gold badges158 silver badges162 bronze badges

3 Comments

jeff ayan Over a year ago

Still getting an Error "A pipeline stage specification object must contain exactly one field ". Is there any problem in passing regex to array field.

Sede Over a year ago

Please edit your question with sample document and the expected result.

jeff ayan Over a year ago

it tried this one too db.collections.aggregate([ {$match:{"Phone Number" : "9159571195","services":{"$regex": /^E/, "$options": "si"}}}, { $project: { Number: '$Phone Number', Name: '$services' } } ])

Collectives™ on Stack Overflow

Filter array elements with $regex

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related