MongoDB query on object name substring

Question

I have uploaded the following JSON file into a mongoDB database: https://cloudpricingcalculator.appspot.com/static/data/pricelist.json

I have succeeded in accessing it using the following line in my app.js:

 db.googlepricelist.find({}, 
  {"gcp_price_list":1}).pipe(JSONStream.stringify()).pipe(res);

I would like to query all objects in object gcp_price_list, where the name of the object contains substring "VMIMAGE". So for example bellow objects:

"CP-COMPUTEENGINE-VMIMAGE-F1-MICRO"
"CP-COMPUTEENGINE-VMIMAGE-G1-SMALL"

I can't figure out how to define a query which is able to do this.

So far I tried this:

    db.googlepricelist.find({$where: function() {
    for (var key in this.gcp_price_list) {
        if (key.indexOf("VMIMAGE")!=-1) {
            return true;
        }
        return false;
    }
},}).pipe(JSONStream.stringify()).pipe(res);

Possible duplicate of Searching for value of any field in MongoDB without explicitly naming it — HRK44
– HRK44, Commented Mar 29, 2018 at 11:29
From there I figured that db.googlepricelist.find({$where: function() { for (var key in this) { if (key.indexOf("VMIMAGE")!=-1) { return true; } return false; } },}).pipe(JSONStream.stringify()).pipe(res); might work, but unfortunately it does not. — K. Tilman
– K. Tilman, Commented Mar 29, 2018 at 13:12

dnickless · Accepted Answer · 2018-03-29 14:08:38Z

0

This should get you going and works from v3.4.4 onwards:

db.googlepricelist.aggregate({
    $project: {
        "gcp_price_list_as_array": { $objectToArray: "$gcp_price_list" }, // transform "gcp_price_list" into an array of key-value pairs
    }
}, {
    $unwind: "$gcp_price_list_as_array" // flatten array
}, {
    $match: { "gcp_price_list_as_array.k": /VMIMVAGE/ } // like filter on the "k" (as in "key") field using regular expression
})

You'd normally attempt to use $filter to filter the array which, however, doesn't work using a regular expression. There's an open JIRA ticket for that but it's simply not yet available.

answered Mar 29, 2018 at 14:08

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

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

4 Comments

K. Tilman Over a year ago

Hi and thanks a lot! It works perfectly. Is there any way to only return the price list? Right now I have to do result[0].gcp_price_list_as_array to get the first object, but I would prefer to do gcp_price_list_as_array[0] Is that possible? Thanks again.

dnickless Over a year ago

I'm not sure I understand... The aggregation query returns a list of matching documents. As a consequence you will have to either iterate that list or access a specific item like result[0]. Can you perhaps rephrase your question?

K. Tilman Over a year ago

Nevermind, I found a solution for that myself. I do have one more question though. Can I store VMIMAGE in a variable of some sort? So that I can say /variablename/ .

dnickless Over a year ago

Yes: stackoverflow.com/questions/26699885/…

Collectives™ on Stack Overflow

MongoDB query on object name substring

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related