How to find substring in array using mongo?

Question

I have following json structure -

{
"_id" : ObjectId("545c4995e4b031360867fe14"), 
"virtualMachines" : [ 
    {
        "vmId" : "vmx-07",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd31d"
        ]
    }, 
    {
        "vmId" : "vmx-08",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd31d",
            "naa.600605b00237d91016cdc38f376bd32d",
            "naa.600605b00237d91016cdc38f376bd33d"
        ]
    }, 
    {
        "vmId" : "vmx-09",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd34d"
        ]
    }, 
    {
        "vmId" : "vmx-04",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd32d"
        ]
    }]
}

I want to find vmId's if canonical name contains following strings in it- 5b00237d91016cdc38f376bd32d or 5b00237d91016cdc38f376bd31d

My expected output is -

"virtualMachines" : [ 
    {
        "vmId" : "vmx-07",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd31d"
        ]
    }, 
    {
        "vmId" : "vmx-08",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd31d",
            "naa.600605b00237d91016cdc38f376bd32d",
            "naa.600605b00237d91016cdc38f376bd33d"
        ]
    }, 
{
        "vmId" : "vmx-04",
        "canonicalName" : [ 
            "naa.600605b00237d91016cdc38f376bd32d"
        ]
    }]

P.S. given ids are substring of canonical name i.e. canonical name naa.600605b00237d91016cdc38f376bd32d contains id 5b00237d91016cdc38f376bd32d.

How do I those find documents of which canonical name array contains given substrings??

Wizard · Accepted Answer · 2014-11-07 05:14:41Z

2

Use aggregate and RegExp can handle it.

// use regular expression according to needs of searching substring 
var criteria = [ new RegExp("5b00237d91016cdc38f376bd31d$"),
                new RegExp("5b00237d91016cdc38f376bd32d$") ];

var match = {
    $match : {
        "virtualMachines.canonicalName" : {
            $in : criteria
        }
    }
};

db.c.aggregate([ 
    // filter to matched documents
    match, 

    // split virtualMachines array and match again to filter out unmatched items
    {
        $unwind : "$virtualMachines"
    }, 
    match, 

    // reshape virtualMachines array
    {
        $group : {
            _id : "$_id",
            virtualMachines : {
                $push : "$virtualMachines"
            }
        }
    } ]).pretty();

edited Nov 7, 2014 at 5:14

answered Nov 7, 2014 at 5:07

Wizard

4,4311 gold badge17 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to find substring in array using mongo?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related