MongoDB Query to find from dynamic fields

Question

I have an issue to find and get data from collection. Here is my collection data

/* 1 */
{
    "_id" : 1,
    "name" : "sue",
    "age" : 19,
    "type" : 1,
    "points" : {
        "A" : {
            "type" : "label",
            "values" : "abc"
        },
        "B" : {
            "mandatory" : false,
            "type" : "text"
        },
        "C" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}

/* 2 */
{
    "_id" : 2,
    "name" : "bob",
    "age" : 42,
    "type" : 1,
    "points" : {
        "B" : {
            "type" : "label",
            "values" : ""
        },
        "C" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}

/* 3 */
{
    "_id" : 3,
    "name" : "ahn",
    "age" : 22,
    "type" : 2,
    "points" : {
        "A" : {
            "type" : "label",
            "values" : "abc"
        },
        "C" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}

/* 4 */
{
    "_id" : 4,
    "name" : "xi",
    "age" : 34,
    "type" : 2,
    "points" : {
        "A" : {
            "type" : "label",
            "allowedValues" : "abc"
        },
        "C" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}

/* 5 */
{
    "_id" : 5,
    "name" : "xyz",
    "age" : 23,
    "type" : 2,
    "points" : {
        "B" : {
            "mandatory" : false,
            "type" : "text"
        },
        "C" : {
            "values" : "C",
            "type" : "text"
        }
    }
}

/* 6 */
{
    "_id" : 6,
    "name" : "abc",
    "age" : 43,
    "type" : 1,
    "points" : {
        "A" : {
            "type" : "label",
            "values" : "abc"
        },
        "B" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}

I want all documents which have "type"="label" and "values"="" from the field "points".

How can I achieve this list using find() in mongoDB? Does anyone have idea about this?

Answer 1

As with the current design, you would need a mechanism to get a list of all the dynamic keys in the collection e.g var dynamic_keys = ["A", "B", "C"], parse this list to create an $or query, which if applied to the above your final query should essentially look like

db.collection.find({ 
    "$or": [ 
        { "points.A.type": "label", "points.A.values": "" }, 
        { "points.B.type": "label", "points.B.values": "" }, 
        { "points.C.type": "label", "points.C.values": "" } 
    ]
})

---

The first operation of getting the dynamic keys list is only possible through Map-Reduce.

Running the following mapreduce operation in mongo shell will populate a separate temp collection called temp_collection_keys with all the dynamic keys as the _id values:

mr = db.runCommand({
    "mapreduce": "collection",
    "map": function() {
        for (var key in this.points) { emit(key, null); }
    },
    "reduce": function() { }, 
    "out": "temp_collection_keys"
})

To get a list of all the dynamic keys, run distinct on the resulting collection:

db[mr.result].distinct("_id")
["A", "B", "C"]

---

Now given the list above, you can assemble your query by creating an object that will have its properties set within a loop. Normally your $or document will have this structure:

var orQuery = {
    "$or": [ 
        { "points.A.type": "label", "points.A.values": "" }, 
        { "points.B.type": "label", "points.B.values": "" }, 
        { "points.C.type": "label", "points.C.values": "" } 
    ]
};

So using the above list of subdocument keys, you can dynamically construct the above $or array using native JavaScript's map() method applied on the distinct array result:

var mr = db.runCommand({
    "mapreduce": "collection",
    "map": function() {
        for (var key in this.points) { emit(key, null); }
    },
    "reduce": function() { }, 
    "out": "temp_collection_keys"
});

var orArray = db[mr.result].distinct("_id").map(function (key){
    var obj = { };
    obj["points."+key+".type"] = "label";
    obj["points."+key+".values"] = "";
    return obj;
}); 

db.collection.find({ "$or": orArray });

Sample Output

{
    "_id" : 2,
    "name" : "bob",
    "age" : 42,
    "type" : 1,
    "points" : {
        "B" : {
            "type" : "label",
            "values" : ""
        },
        "C" : {
            "mandatory" : false,
            "type" : "text"
        }
    }
}