Mongo + check multiple fields existing

Question

I am working mongo with nodejs.

I have array list:

var checkFields = ["field1","field2","field3"];

I try to get the count of records having the array list fields and user field is equal to admin.

Sample data:

[
    {  
        "checkFields": { 
            "field1": "00124b3a5c31", 
            "user": "admin" 
        } 
    },
    {  
        "checkFields": { 
            "field2": "00124b3a5c31", 
            "user": "admin" 
        } 
    },
    { 
        "checkFields": { 
            "field1": "00124b3a5c31", 
            "user": "regular" 
        } 
    }
]

Query:

db.collection_name.find(
    {"checkFields.user" : "admin"} 
    { "checkFields.field1": { $exists: true} } 
)

Expected Result:

Result is to get rows of count of matching the field in array list(checkFields).

Answer 1

Building up an $or array for the list of field existence checks is the right approach, but assuming you're on a current node.js build you can simplify the query creation to:

var checkFieldsLists = checkFields.map(field => ({
    ['checkFields.' + field]: {$exists: true}
}));
var query = {
    $or: checkFieldsLists,
    'checkFields.user': 'admin'
}

This removes the superfluous $or for the "user is admin" check which lets you also remove the outer $and, so that the generated query is:

{ '$or':
   [ { 'checkFields.field1': { '$exists': true } },
     { 'checkFields.field2': { '$exists': true } },
     { 'checkFields.field3': { '$exists': true } } ],
  'checkFields.user': 'admin' }

Answer 2

Here is the solution using aggregate query.

var Db = require('mongodb').Db, Server = require('mongodb').Server, assert = require('assert');

var db = new Db('localhost', new Server('localhost', 27017));

var checkFields = ["field1", "field2", "field3"];

var checkFieldsLists = [];
for (var i = 0; i < checkFields.length; i++) {
    var jsObj = {};
    jsObj['checkFields.' + checkFields[i]] = {};
    jsObj['checkFields.' + checkFields[i]].$exists = true;
    checkFieldsLists.push(jsObj);
}

var query = {
    "$and" : [{
        "$or" : checkFieldsLists
    }, {
        "$or" : [{
            "checkFields.user" : "admin"
        }]
    }]
}; 

var matchQuery = {
    "$match" : {
        "checkFields.user" : "admin",
        "$or" :  checkFieldsLists 

    }
};

var groupQuery = {
    $group : {
        _id : null,
        count : {
            $sum : 1
        }
    }
};

var aggregateCheckFields = function(db, callback) {
    console.log("Match query is ====>" + JSON.stringify(matchQuery));
    console.log("Group query is ====>" + JSON.stringify(matchQuery));
    db.collection('checkfields').aggregate([ matchQuery, groupQuery ]).toArray(
            function(err, result) {
                assert.equal(err, null);
                console.log("Result is ===>" + JSON.stringify(result));
                if (result.length > 0) {
                    console.log("Count is ===>" + result[0].count); 
                }               
                callback(result);
            });
};

db.open(function(err, db) {

    aggregateCheckFields(db, function() {
        db.close();
    });

});

Output:-

Result is ===>[{"_id":null,"count":3}]
Count is ===>3

Answer 3

I tried the following code. Its working but don't know its good solution and perfomance. Please anyone have better answer means please post it.

var checkFields = ["field1", "field2", "field3"];

var checkFieldsLists = [];
for ( i = 0; i < checkFields.length; i++) {
    var jsObj = {};
    jsObj['checkFields.' + checkFields[i]] = {};
    jsObj['checkFields.' + checkFields[i]].$exists = true;
    checkFieldsLists.push(jsObj);
}

var query = {
    "$and" : [{
        "$or" : checkFieldsLists
    }, {
        "$or" : [{
            "checkFields.user" : "admin"
        }]
    }]
};

console.log(JSON.stringify(query));
//console log will return
/*
   {"$and":[{
        "$or" : [{
            "checkFields.field1" : {
                "$exists" : true
            }
        }, {
            "checkFields.field2" : {
                "$exists" : true
            }
        }, {
            "checkFields.field3" : {
                "$exists" : true
            }
        }]
    }, {
        "$or" : [{
            "checkFields.user" : "admin"
        }]
    }]
}
*/
collection.find(query);