Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Index Sub-Documents in MongoDB

DZone's Guide to

Index Sub-Documents in MongoDB

· Database Zone ·
Free Resource

Built by the engineers behind Netezza and the technology behind Amazon Redshift, AnzoGraph is a native, Massively Parallel Processing (MPP) distributed Graph OLAP (GOLAP) database that executes queries more than 100x faster than other vendors.  

Let’s say I have a collection of documents that all kind of look like this:

> db.foos.find().pretty()
{
    "_id" : ObjectId("511fe286777e76dfdddbf440"),
    "foo" : "bar",
    "timestamp" : ISODate("2013-02-16T20:02:39.417Z")
}

Then, all of a sudden I get a requirement handed to me that this collection needs to be searched for all documents with a timestamp within a given hour, on a given month, within a given year. Writing a query for that is going be a bit gnarly, so we create a sub-document in our documents that makes this query easier.

{
    "_id" : ObjectId("511fe286777e76dfdddbf440"),
    "foo" : "bar",
    "timestamp" : ISODate("2013-02-16T20:02:39.417Z"),
    "timefields" : {
        "y" : 2013,
        "mo" : 2,
        "d" : 16,
        "h" : 20,
        "mi" : 02,      
    }
}

So now my query will look like the following and all will be happy in the world. And yes, I could have put each of these elements at the top level, but bear with me for demonstration purposes…

> db.foos.find({"timefields.y":2013, "timefields.mo": 2, "timefields.h":20}).pretty()
{
    "_id" : ObjectId("511fe286777e76dfdddbf440"),
    "foo" : "bar",
    "timestamp" : ISODate("2013-02-16T20:02:39.417Z"),
    "timefields" : {
        "y" : 2013,
        "mo" : 2,
        "d" : 16,
        "h" : 20,
        "mi" : 2
    }
}

Ok, but what about that index that was (hypothetically) on timestamp? Because we are searching other fields we aren’t using indexes in our query and the search is happening in linear time. The horror!

Well, mongo is flexible if it isn’t anything else. You can just as easily index fields in a sub-document like you can any other field. For instance:

> var indices = { "timefields.h" : 1,
... "timefields.y" : 1,
... "timefields.mo" : 1,
... "timefields.d" : 1,
... "timefields.m" : 1}
> db.foos.ensureIndex(indices)

> db.foos.getIndices()
[
    ...
    {
        "v" : 1,
        "key" : {
            "timefields.h" : 1,
            "timefields.y" : 1,
            "timefields.mo" : 1,
            "timefields.d" : 1,
            "timefields.m" : 1
        },
        "ns" : "test.foos",
        "name" : "timefields.h_1_timefields.y_1_timefields.mo_1_timefields.d_1_timefields.m_1"
    }
]

And now when I run explain on the cursor using the same query I performed before, you can now see those indices are being used:

> db.foos.find({"timefields.y":2013, "timefields.mo": 2, "timefields.h":20}).pretty().explain()
{
    ...
    "indexBounds" : {
        "timefields.h" : [
            [
                20,
                20
            ]
        ],
        "timefields.y" : [
            [
                2013,
                2013
            ]
        ],
        "timefields.mo" : [
            [
                2,
                2
            ]
        ],
        "timefields.d" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ],
        "timefields.m" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
}


 

Download AnzoGraph now and find out for yourself why it is acknowledged as the most complete all-in-one data warehouse for BI style and graph analytics.  

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}