Testing Multi-key Indexing

MongoTesting.js

// Array of JSON Objects where each JSON object has a non-distinct key name
// note that `records`'s array element row has name, age and hair
// Note-  update at bottom showing how to use $elemMatch to achieve what the initial find()'s could not
db.test3.find()
{ "_id" : 1, "records" : [  {  "name" : "ralph",  "age" : "55",  "hair" : "brown" } ] } 
{ "_id" : 2, "records" : [  {  "name" : "bill",  "age" : "25",  "hair" : "blue" } ] }
{ "_id" : 3, "records" : [  {  "name" : "joe",  "age" : "35",  "hair" : "blonde" } ] }

 >
> db.test3.ensureIndex( { records: 1} )
> db.test3.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.test3",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "records" : 1
                },
                "ns" : "test.test3",
                "name" : "records_1"
        }
]
// the find uses the index, yet scans *0* objects
> db.test3.find({records:{hair:"brown"}}).explain()
{
        "cursor" : "BtreeCursor records_1",
        "isMultiKey" : false,
        "n" : 0,
        "nscannedObjects" : 0,
        "nscanned" : 0,
        "nscannedObjectsAllPlans" : 0,
        "nscannedAllPlans" : 0,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "records" : [
                        [
                                {
                                        "hair" : "brown"
                                },
                                {
                                        "hair" : "brown"
                                }
                        ]
                ]
        },
        "server" : "ACNU3299MQS:27017"
}

// Lessons Learned (courtesy of Ross Lawley)
// (1) indexing on `records` enables searching on `records` JSON 
//         objects that match **exactly**
// (2) index size cannot exceed 1024 Bytes

> db.test3.find()
{ "_id" : 1, "records" : [  {  "name" : "ralph",  "age" : "55",  "hair" : "brown" } ] }
{ "_id" : 2, "records" : [  {  "hair" : "brown" } ] }
{ "_id" : 3, "records" : [  {  "hair" : "brown",  "age" : "100" } ] }

> db.test3.ensureIndex({"records" : 1})
> db.test3.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.test3",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "records" : 1
                },
                "ns" : "test.test3",
                "name" : "records_1"
        }
]

// returns 1 since only 1 document has a "records" array with a 
// JSON object matching {hair : "brown"} **exactly**

> db.test3.find({records : {hair : "brown"}})
{ "_id" : 2, "records" : [  {  "hair" : "brown" } ] }
>
>
> db.test3.find({records : {hair : "brown"}}).explain()
{
        "cursor" : "BtreeCursor records_1",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "records" : [
                        [
                                {
                                        "hair" : "brown"
                                },
                                {
                                        "hair" : "brown"
                                }
                        ]
                ]
        },
        "server" : "ACNU3299MQS:27017"
}
>

/* Note, however, that $elemMatch can be used to find a single match inside of an array */
> db.test.find()
{ "_id" : 1, "records" : [  {  "name" : "ralph",  "age" : "55",  "hair" : "brown" } ] }
{ "_id" : 2, "records" : [  {  "name" : "bill",  "age" : "25",  "hair" : "blue" } ] }
{ "_id" : 3, "records" : [  {  "name" : "joe",  "age" : "35",  "hair" : "blonde" } ] }
>
>
> db.test.find( { records : {$elemMatch : { "name" : "bill" } }} )
{ "_id" : 2, "records" : [  {  "name" : "bill",  "age" : "25",  "hair" : "blue" } ] }

> db.test.find( { records : {$elemMatch : { "name" : "bill", "age" : "25" } }} )
{ "_id" : 2, "records" : [  {  "name" : "bill",  "age" : "25",  "hair" : "blue" } ] }
>