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

Question

I want to perform a query on this collection to determine which documents have any keys in things that match a certain value. Is this possible?

I have a collection of documents like:

{
    "things": {
        "thing1": "red",
        "thing2": "blue",
        "thing3": "green"
    }
}

EDIT: for conciseness

Solution 1

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 2

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 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.

Comments

[JohnnyHK]

Right, I mistakenly read his question as he couldn't change the model, but yeah, if you can you'd want to do something like this so that your keys are fixed.

[autodidacticon]

@WiredPrairie What problems exist with the original schema that make the collection hard to query / index?

[WiredPrairie]

I explained that .. the fields weren't indexable with the original structure. You can't have an arbitrary key be indexed.

[TBE]

lets say i want to perform an update on one of the values using mongoose, how can i achieve that? What if i have an array that contains settingB and settingsC with a value to update. what will be the must efficient way of doing so?

[WiredPrairie]

@TBE - please ask a new question rather than adding your question as a comment.

[ekkis]

but this doesn't answer the question. the question is "Is this possible?" (given the data morphology presented). a better answer would be: no, mongodb is an object data store that is pathetically incapable of retrieving objects by key and you have to convert your data to an array

[Ant D]

@ekkis - came across this, stackoverflow.com/questions/19868016/…