CODEWITHSUNDEEP
mongodb
Ghislain Fourny
Ghislain Fourny

Reputation: 7279

MongoDB query to return documents that only have keys amongst a predefined set

The MongoDB query language allows filtering documents based on the existence or absence of a given field with the $exists operator.

Is there a way, with the MongoDB syntax, and given a set K of allowed fields, to exclude documents that have fields not in K from the results, but:

Example:

{
  "Some field" : "foo"
}
{
  "Some field" : "bar",
  "Some other field" : "foobar"
}

With the set K = [ "Some field" ], only the first document is to be returned.

Note how this is not to be confused with a projection, which would return both documents but removing the extra field.

Upvotes: 1

Views: 82

Answers (2)

Saleem
Saleem

Reputation: 8978

I'm not sure if MongoDB do support such kind of operations out of box but you can achieve so with help of mapReduce.

Assuming your sample data set;

// Variable for map
var map = function () {
    var isAcceptable = true;
    Object.keys(this).forEach(function (key) {
        if (key != "_id" && white_list.indexOf(key) == -1) {
            isAcceptable = false;
        }
    });

    if (isAcceptable == true) {
        emit(1, this);
    }
};

// Variable for reduce
var reduce = function (key, values) {
    return values;
};

db.collection.mapReduce(
    map,
    reduce,
    {
        scope: {"white_list": ["Some field"]},
        out: {"inline": 1}
    }
);

Will return:

{ 
    "results" : [
        {
            "_id" : 1, 
            "value" : {
                "_id" : ObjectId("57cd7503e55de957c62fb9c8"), 
                "Some field" : "foo"
            }
        }
    ], 
    "timeMillis" : 13, 
    "counts" : {
        "input" : 2, 
        "emit" : 1, 
        "reduce" : 0, 
        "output" : 1
    }, 
    "ok" : 1
}

Desired result will be in results.values of returned document. However, keep in mind limitation of MongoDB mapReduce and maximum size of BSON document.

Upvotes: 1

chridam
chridam

Reputation: 103365

Given a set of known fields K, you can construct a query that takes the set as input and gives a query with the $exists operator along with the corresponding fields projection. Using an example, suppose you have the following documents in a test collection

db.test.insert({ "fieldX": "foo", "fieldY": "bar", "fieldZ": 1 })
db.test.insert({ "fieldX": "123", "fieldY": "bar", "fieldZ": 2 })
db.test.insert({ "fieldY": "abc", "fieldZ": 3 })
db.test.insert({ "fieldX": "xyz", "fieldZ": 4 })
db.test.insert({ "fieldZ": 5 })

Then you can construct a query Q and a projection P from an input set K as follows:

var K = [ "fieldX", "fieldZ" ];  

var or = K.map(function(field) { 
    var obj = {};
    obj[field] = { "$exists": true };
    return obj;
});

var P = K.reduce(function(doc, field) { 
    doc[field] = 1;
    return doc;
}, {} );

var Q = { "$or": or };

db.test.find(Q, P);

Sample Output:

/* 1 */
{
    "_id" : ObjectId("57cd78322c241f5870c82b7d"),
    "fieldX" : "foo",
    "fieldZ" : 1
}

/* 2 */
{
    "_id" : ObjectId("57cd78332c241f5870c82b7e"),
    "fieldX" : "123",
    "fieldZ" : 2
}

/* 3 */
{
    "_id" : ObjectId("57cd78332c241f5870c82b7f"),
    "fieldZ" : 3
}

/* 4 */
{
    "_id" : ObjectId("57cd78332c241f5870c82b80"),
    "fieldX" : "xyz",
    "fieldZ" : 4
}

/* 5 */
{
    "_id" : ObjectId("57cd78332c241f5870c82b81"),
    "fieldZ" : 5
}

Upvotes: 0

Related Questions