MongoDB efficient way to search for objectId field with null using Index?

Question

I am trying to find an efficient way to search for items in which specific field is null.

In the MongoDB, I have folder schema which has parent field of its parent folder's ObjectId and parent is indexed. For root folders, parent fields are null.

When I try to find all the root folders with parent:null, explain displays indexOnly: false

db.folders.find({parent: null}, {parent: 1, _id: 0}).explain()
{
    "cursor" : "BtreeCursor parent_1",
    "isMultiKey" : false,
    "n" : 126,
    "nscannedObjects" : 126,
    "nscanned" : 126,
    "nscannedObjectsAllPlans" : 126,
    "nscannedAllPlans" : 126,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 1,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "parent" : [ 
            [ 
                null, 
                null
            ]
        ]
    },
    "server" : "c268.candidate.36:10268",
    "filterSet" : false,
    "stats" : {
        "type" : "PROJECTION",
        "works" : 128,
        "yields" : 1,
        "unyields" : 1,
        "invalidates" : 0,
        "advanced" : 126,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,
        "children" : [ 
            {
                "type" : "KEEP_MUTATIONS",
                "works" : 128,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 126,
                "needTime" : 1,
                "needFetch" : 0,
                "isEOF" : 1,
                "children" : [ 
                    {
                        "type" : "FETCH",
                        "works" : 128,
                        "yields" : 1,
                        "unyields" : 1,
                        "invalidates" : 0,
                        "advanced" : 126,
                        "needTime" : 1,
                        "needFetch" : 0,
                        "isEOF" : 1,
                        "alreadyHasObj" : 0,
                        "forcedFetches" : 0,
                        "matchTested" : 126,
                        "children" : [ 
                            {
                                "type" : "IXSCAN",
                                "works" : 127,
                                "yields" : 1,
                                "unyields" : 1,
                                "invalidates" : 0,
                                "advanced" : 126,
                                "needTime" : 1,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "keyPattern" : "{ parent: 1.0 }",
                                "isMultiKey" : 0,
                                "boundsVerbose" : "field #0['parent']: [null, null]",
                                "yieldMovedCursor" : 0,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0,
                                "keysExamined" : 126,
                                "children" : []
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

I tried to use $type to find any ones which parent folder is not ObjectId. But still indexOnly:false.

db.folders.find({parent: {$ne: {$type: 7}}}, {parent: 1, _id: 0}).explain() "indexOnly": false

Is there a way to search null value only using index? If not, is there a better value to store instead of null to be able to search with index?

Additional

example of root folder

{
  "_id" : ObjectId("55a04a2d754971030059b7ad"),
  "active" : true,
  "modified" : ISODate("2016-02-05T22:30:08.053Z"),
  "created" : ISODate("2015-07-10T22:41:49.009Z"),
  "user" : ObjectId("54d3ae187a738c0300f59e61"),
  "name" : "2nd Grade",
  "parent" : null,
  "clientModified" : ISODate("2016-02-05T22:30:07.872Z"),
  "userCreated" : ISODate("2015-07-10T22:41:48.328Z"),
  "ancestors" : [],
  "__v" : 2
}

example of child folder

{
  "_id" : ObjectId("56d0b4edb6f05e03009bcabc"),
  "active" : true,
  "modified" : ISODate("2016-02-26T20:26:21.328Z"),
  "created" : ISODate("2016-02-26T20:26:21.328Z"),
  "user" : ObjectId("54d3ae187a738c0300f59e61"),
  "name" : "music",
  "parent" : ObjectId("55a04a2d754971030059b7ad"),
  "clientModified" : ISODate("2016-02-26T20:26:20.398Z"),
  "userCreated" : ISODate("2016-02-26T20:26:20.398Z"),
  "ancestors" : [ 
    ObjectId("55a04a2d754971030059b7ad")
  ],
  "__v" : 0
}

db.folders.getIndexes()

{
    "0" : {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "app29099188.folders"
    },
    "1" : {
        "v" : 1,
        "key" : {
            "user" : 1,
            "_fts" : "text",
            "_ftsx" : 1
        },
        "name" : "user_1_name_text",
        "ns" : "app29099188.folders",
        "background" : true,
        "safe" : null,
        "weights" : {
            "name" : 1
        },
        "default_language" : "english",
        "language_override" : "language",
        "textIndexVersion" : 2
    },
    "2" : {
        "v" : 1,
        "key" : {
            "user" : 1,
            "parent" : 1
        },
        "name" : "user_1_parent_1",
        "ns" : "app29099188.folders",
        "background" : true,
        "safe" : null
    },
    "3" : {
        "v" : 1,
        "key" : {
            "parent" : 1.0000000000000000
        },
        "name" : "parent_1",
        "ns" : "app29099188.folders"
    }
}

could you post example of parent and child document? – profesor79 Mar 03 '16 at 21:49 — profesor79, Mar 03 '16 at 21:49
Also can you post db.folders.getIndexes() ? – James Wahlin Mar 03 '16 at 21:58 — James Wahlin, Mar 03 '16 at 21:58
btw - can you use zeroed ID instead of null entry? – profesor79 Mar 04 '16 at 22:48 — profesor79, Mar 04 '16 at 22:48
my edited answer have a example with not null parent field – profesor79 Mar 04 '16 at 23:07 — profesor79, Mar 04 '16 at 23:07

profesor79 · Answer 1 · 2016-03-04T23:07:14.840

After comment - update: The way to eliminate docScan is to have a value in parent field. It can be zeroed objectId or just "/" as a root.

db.satoko.insert({"test":"sdsf", parent: "/"})   
db.satoko.insert({"test":"sds33f", parent: "/"})         
db.satoko.insert({"parent":ObjectId("56d8b2879bd059e7247a6096"), "test":"sdsf"})

explain results:

db.satoko.find({parent:{$eq:"/"} }, {parent: 1, _id: 0}).explain("allPlansExec
ution")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.satoko",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "parent" : {
                                "$eq" : "/"
                        }
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "parent" : 1,
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "parent" : 1
                                },
                                "indexName" : "parent_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "parent" : [
                                                "[\"/\", \"/\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 2,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 2,
                "totalDocsExamined" : 0,
                "executionStages" : {
                        "stage" : "PROJECTION",
                        "nReturned" : 2,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 3,
                        "advanced" : 2,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "transformBy" : {
                                "parent" : 1,
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 2,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 3,
                                "advanced" : 2,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "parent" : 1
                                },
                                "indexName" : "parent_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "parent" : [
                                                "[\"/\", \"/\"]"
                                        ]
                                },
                                "keysExamined" : 2,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                        }
                },
                "allPlansExecution" : [ ]
        },
        "serverInfo" : {
                "host" : "greg",
                "port" : 27017,
                "version" : "3.2.3",
                "gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"
        },
        "ok" : 1
}

My explain shows that it uses index too but it is looking at the documents after that. (`nscannedObjects` is larger than 0). Could you share your `exesutionStats`? I think `totalDocsExamined` should be 0 if it only uses index. Thanks. — Satoko, Mar 04 '16 at 22:21
you right, if IXSCAN is after FETCH that means query is not index covered.... so null will always be in FETCH stage (was testing with partial index - but same results) — profesor79, Mar 04 '16 at 22:47

MongoDB efficient way to search for objectId field with null using Index?

1 Answers1