MongoDB nested element query based on value of property

Question

For below mongo document, I am trying to write a projection query. Given an input of userId value and ObectId of a target (ex: 54073d80e4b0cbf1ce225f02 in below document), how do I find the document which has these values?

All the examples, I searched expect the user to know the "property" names to query against. In this case, property names in "target" element are not known ahead of time. They can be my-target-1, my-target-2 etc. SO I want to search the document based on the value of the property (54073d80e4b0cbf1ce225f02). Any suggestions?

{
    "_id" : ObjectId("540786bbe4b0e4752fe93321"),
    "name" : "foo name",
    "userId" : "123456",
    "targets" : {
        "my-target-1" : { //my-target-1 is dynamic property.
            "executionOrder" : {
                "1" : { //"1" is dynamic property.
                    "_id" : ObjectId("54073d80e4b0cbf1ce225f02"),// this is my search key
                    "type" : "TYPE 1",
                    "version" : "2"
                },
             "2" : {
                    "_id" : ObjectId("54073d80e4b0cbf1ce225f03"),
                    "type" : "TYPE 2",
                    "version" : "2"
                }
            }
        },
        "my-target-2" : {
            "executionOrder" : {
                "1" : {
                    "_id" : ObjectId("54073d80e4b0cbf1ce225f04"),
                    "type" : "TYPE 1",
                    "version" : "2"
                },
             "2" : {
                    "_id" : ObjectId("54073d80e4b0cbf1ce225f05"),
                    "type" : "TYPE 2",
                    "version" : "2"
                }
            }
        }
    },

you need a different schema to be able to search this way, OR you need to have enumeration of all the possible key names (list of ["my-target-1","my-target-2", etc] to construct the query. — Asya Kamsky, Sep 05 '14 at 16:43
my question seems to be similar to http://stackoverflow.com/questions/19802502/mongodb-query-help-query-on-values-of-any-key-in-a-sub-object/19802670#19802670. However, value to search in my case is 2 levels deep. I opted to changed the schema — suman j, Sep 06 '14 at 02:41

score 1 · Answer 1 · edited May 23 '17 at 12:22

One of the solutions would be to use the Text search feature of MongoDB to achieve this,

To allow for text search on the fields with string content, use the wildcard specifier ($**) to index all fields that contain string content.

The following example indexes any string value in the data of every field of every document in collection and names the index TextIndex:

db.collection.ensureIndex(
{ "$**": "text" }
)

and query for any text in any of the fields, the below will return you all the documents whose any of the fields in the document or its sub documents have matched the query string.

db.collection.aggregate([{$match:{$text:{$search:"searchString"}}}])

Note: your search will only return results if you index on text fields with string content.

And remember,

The query matches on the complete stemmed words. For example, if a document field contains the word blueberry, a search on the term blue will not match the document. However, a search on either blueberry or blueberries will match

You need to be wise in using this to fit in to your requirement.

So, in your above example, in the subdocuments, you could make search on fields that are unique and are textual.

"_id" : ObjectId("54073d80e4b0cbf1ce225f02") // don't query this field.

rather query for "type" : "TYPE 1".

See Also:

Create text index on sub-document field wildcard

text search is not an option unfortunately as I need to query against ObjectId property — suman j, Sep 06 '14 at 02:37

score 1 · Answer 2 · answered Jul 14 '16 at 19:22

If you want to find documents that contain a certain value you can use $where clause with a function. The query below creates a function that iterates through all properties and values looking for certain value without knowing the property.

 {$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")
    }}

Of course you can modify the query in order to match pairs of certain keys and values. Just like this:

 {$where: function() {
        var deepIterate = function  (obj, tested) {
            for (var field in obj) {

                if (field == tested[0]){
                    if (obj[field] == tested[1]){
                        return true;
                    }
                }

                var found = false;
                if ( typeof obj[field] === 'object') {
                    found = deepIterate(obj[field], tested)
                    if (found) { return true; }
                }
            }
            return false;
        };
        return deepIterate(this, ["myKey", "myvalue"])
    }}

Obviously you can modify the function to query for many matching pairs of keys and values inside a object. Sky is the limit.

MongoDB nested element query based on value of property

2 Answers2