MongoDB Query Help - query on values of any key in a sub-object

Solution 1:

If you don't know what the keys will be and you need it to be interactive, then you'll need to use the (notoriously performance challenged) $where operator like so (in the shell):

db.test.find({$where: function() { 
    for (var field in this.settings) { 
        if (this.settings[field] == "red") return true;
    }
    return false;
}})

If you have a large collection, this may be too slow for your purposes, but it's your only option if your set of keys is unknown.

MongoDB 3.6 Update

You can now do this without $where by using the $objectToArray aggregation operator:

db.test.aggregate([
  // Project things as a key/value array, along with the original doc
  {$project: {
    array: {$objectToArray: '$things'},
    doc: '$$ROOT'
  }},

  // Match the docs with a field value of 'red'
  {$match: {'array.v': 'red'}},

  // Re-project the original doc
  {$replaceRoot: {newRoot: '$doc'}}
])

Solution 2:

I'd suggest a schema change so that you can actually do reasonable queries in MongoDB.

From:

{
    "userId": "12347",
    "settings": {
        "SettingA": "blue",
        "SettingB": "blue",
        "SettingC": "green"
    }
}

to:

{
    "userId": "12347",
    "settings": [
        { name: "SettingA", value: "blue" },
        { name: "SettingB", value: "blue" },
        { name: "SettingC", value: "green" }
    ]    
}

Then, you could index on "settings.value", and do a query like:

db.settings.ensureIndex({ "settings.value" : 1})

db.settings.find({ "settings.value" : "blue" })

The change really is simple ..., as it moves the setting name and setting value to fully indexable fields, and stores the list of settings as an array.

If you can't change the schema, you could try @JohnnyHK's solution, but be warned that it's basically worst case in terms of performance and it won't work effectively with indexes.

Solution 3:

Sadly, none of the previous answers address the fact that mongo can contain nested values in arrays or nested objects.

THIS IS THE CORRECT QUERY:

{$where: function() {
    var deepIterate = function  (obj, value) {
        for (var field in obj) {
            if (obj[field] == value){
                return true;
            }
            var found = false;
            if ( typeof obj[field] === 'object') {
                found = deepIterate(obj[field], value)
                if (found) { return true; }
            }
        }
        return false;
    };
    return deepIterate(this, "573c79aef4ef4b9a9523028f")
}}

Since calling typeof on array or nested object will return 'object' this means that the query will iterate on all nested elements and will iterate through all of them until the key with value will be found.

You can check previous answers with a nested value and the results will be far from desired.

Stringifying the whole object is a hit on performance since it has to iterate through all memory sectors one by one trying to match them. And creates a copy of the object as a string in ram memory (both inefficient since query uses more ram and slow since function context already has a loaded object).

The query itself can work with objectId, string, int and any basic javascript type you wish.