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](https://stackoverflow.com/questions/6790819/searching-for-value-of-any-field-in-mongodb-without-explicitly-naming-it) — HRK44, Mar 29 '18 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, Mar 29 '18 at 13:12

score 0 · Answer 1 · answered Mar 29 '18 at 14:08

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 '18 at 14:08

dnickless

10,733
1
19
34

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. – K. Tilman Mar 29 '18 at 15:02
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? – dnickless Mar 29 '18 at 15:36
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/ . – K. Tilman Mar 29 '18 at 15:55
Yes: https://stackoverflow.com/questions/26699885/how-can-i-use-a-regex-variable-in-a-query-for-mongodb – dnickless Mar 29 '18 at 16:35

MongoDB query on object name substring

1 Answers1