49

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

3 Answers3

58

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'}}
])
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
54

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.

Community
  • 1
  • 1
WiredPrairie
  • 58,954
  • 17
  • 116
  • 143
  • 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. – JohnnyHK Nov 06 '13 at 03:15
  • @WiredPrairie What problems exist with the original schema that make the collection hard to query / index? – autodidacticon Jan 04 '15 at 19:31
  • I explained that .. the fields weren't indexable with the original structure. You can't have an arbitrary key be indexed. – WiredPrairie Jan 04 '15 at 21:16
  • 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? – TBE Jan 25 '16 at 15:06
  • @TBE - please ask a new question rather than adding your question as a comment. – WiredPrairie Jan 25 '16 at 20:49
  • 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 – ekkis Mar 30 '19 at 05:19
  • 1
    @ekkis - came across this, https://stackoverflow.com/questions/19868016/check-that-field-exists-with-mongodb – Ant D Nov 19 '21 at 14:28
  • While this is a good recommendation, it doesn't answer the original question and shouldn't be the accepted answer. – pmont Oct 28 '22 at 16:13
8

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.

twboc
  • 1,452
  • 13
  • 17