How to get a document in mongodb only if at least 3 columns have non null values -

How to get a document in mongodb only if at least 3 columns have non null values -

i have collection in mongodb pupil attributes id, name, standard, marks, average. want write query documents @ to the lowest degree 3 attributes contain non-null values.

all documents contain non null value in (name, standard, marks) or (name,marks,average) or (name,standard,marks,average) or (id, name, standard, marks, average) should printed. if document contains (name,standard) non null or (standard,marks) should ignored.

i have said given "students" documents this:

class="lang-js prettyprint-override">{ name: "a", standard: "b", marks: 10 }, { name: "b", marks: 5, average: 2 }, { id: 2, name: "c", marks: 10, average: 7 }, { name: "c", standard: "b" }, { standard: "c", marks: 3 }

then "ideally" this:

class="lang-js prettyprint-override">db.students.find({ "$or": [ { "$and": [ { "name": { "$exists": true } }, { "name": { "$ne": null } }, { "standard": { "$exists": true } }, { "standard": { "$ne": null } }, { "marks": { "$exists": true } }, { "marks": { "$ne": null } }, ], }, { "$and": [ { "name": { "$exists": true } }, { "name": { "$ne": null } }, { "marks": { "$exists": true } }, { "marks": { "$ne": null } }, { "average": { "$exists": true } }, { "average": { "$ne": null } } ], }, { "$and": [ { "name": { "$exists": true } }, { "name": { "$ne": null } }, { "marks": { "$exists": true } }, { "marks": { "$ne": null } }, { "standard": { "$exists": true } }, { "standard": { "$ne": null } }, { "average": { "$exists": true } }, { "average": { "$ne": null } } ], }, { "$and": [ { "id": { "$exists": true } }, { "id": { "$ne": null } }, { "name": { "$exists": true } }, { "name": { "$ne": null } }, { "marks": { "$exists": true } }, { "marks": { "$ne": null } }, { "standard": { "$exists": true } }, { "standard": { "$ne": null } }, { "average": { "$exists": true } }, { "average": { "$ne": null } } ], } ] })

which excludes lastly 2 documents.

also in modern mongodb 2.6 , greater versions index intersection, or version of such in 2.4 versions considering $or operand. can index so:

class="lang-js prettyprint-override"> db.student.ensureindex({ "name": 1, "standard": 1, "marks": 1 }) db.student.ensureindex({ "name": 1, "marks": 1, "average": 1 }) db.student.ensureindex({ "name": 1, "marks": 1, "standard": 1, "average": 1 }) db.student.ensureindex({ "id": 1, "name": 1, "marks": 1, "standard": 1, "average": 1 })

that can add together lot of "index" space usage, means may outweigh ends in case.

of course of study more flexible approach determining (though not fast) can approach aggregation framework:

class="lang-js prettyprint-override">db.students.aggregate([ { "$project": { "id": { "$ifnull": [ "$id", null ] }, "name": { "$ifnull": [ "$name", null ] }, "marks": { "$ifnull": [ "$marks", null ] }, "standard": { "$ifnull": [ "$standard", null ] }, "average": { "$ifnull": [ "$average", null ] }, "fields": { "$add": [ { "$cond": [ { "$ifnull": [ "$id", null ] }, 1, 0 ] }, { "$cond": [ { "$ifnull": [ "$name", null ] }, 1, 0 ] }, { "$cond": [ { "$ifnull": [ "$marks", null ] }, 1, 0 ] }, { "$cond": [ { "$ifnull": [ "$standard", null ] }, 1, 0 ] }, { "$cond": [ { "$ifnull": [ "$average", null ] }, 1, 0 ] }, ] } }}, { "$match": { "fields": { "$gte": 3 } } } ])

which more "literal" interpretation of question if limited aggregation framework constraint of needing declare all of "fields" possible.

the $ifnull operator 1 doing "heavy lifting", replacing "non-existant" or null fields null value evaluation. "try" filter $match in initial pipeline stage much done in first query cut down input.

the final real grab comes in if have **too many* varying field combinations specify in either form , need know "three" or more of fields exist or not null.

this approach comes downwards using $where form of evaluation, to the lowest degree efficient way handle general query, flexible since javascript code can handle these situations:

class="lang-js prettyprint-override">db.students.find( function() { var count = 0; ( var k in ) { if ( ( k != null) && ( k != "_id") ) { count++; if ( count >= 3 ) break; } } homecoming ( count >= 3 ); } )

so while lastly forms "looks" simple, pretty horrible there no way avoid ends "full collection scan", fields in each document evaluated conditions in javascript. @ to the lowest degree until count of "three" reached.

that gives few approaches. first 1 suits.

mongodb mongodb-query