mongodb-querybuilder

mongodb-querybuilder is a javascript helper that provides some convenience methods to easily build MongoDB Aggregation Framework pipelines. It is based on the concept of Fluent APIs and automatically handles a lot of the necessary pipeline stages that are just a means to an end (like $unwinding, $projecting nested fields to the top level, etc).

Installation

npm install

Testing

npm test

And then open http://localhost:8080/__zuul in a browser.

Dist

Of course the preferred way to use -querybuilder is npm+browserify, but sometimes being able to just drop a script tag into a codepen is nedded.

Create a GitHub Access Token
Export your access token as GITHUB_TOKEN={{YOUR TOKEN}}
npm run-script dist

Todo

Setup travis for saucelabs. see zuul docs
Write lots more tests

Example

The query builder turns that:

builder
    .match("fields.reporter.name", ["thomasr", "ramon.fernandez", "spencer"])
    .match("fields.components.name", ["Security", "Sharding"])
    .match("changelog.total", [10, 50])
    .group("x-axis", ["fields.fixVersions.name", "fields.status"])
        .agg("y-axis", "$sum", 1)
        .agg("size", "$avg", "changelog.total")
        .agg("_ids", "$push", "_id")
    .limit(5);

into that:

[
    {
        "$match": {
            "fields.reporter.name": {
                "$in": [
                    "thomasr",
                    "ramon.fernandez",
                    "spencer"
                ]
            },
            "fields.components.name": {
                "$in": [
                    "Security",
                    "Sharding"
                ]
            },
            "changelog.total": {
                "$gte": 10,
                "$lte": 50
            }
        }
    },
    {
        "$unwind": "$fields.fixVersions"
    },
    {
        "$project": {
            "fields_fixVersions_name": "$fields.fixVersions.name",
            "fields_status": "$fields.status",
            "changelog_total": "$changelog.total",
            "_id": "$_id"
        }
    },
    {
        "$group": {
            "_id": {
                "fields_fixVersions_name": "$fields_fixVersions_name",
                "fields_status": "$fields_status"
            },
            "y-axis": {
                "$sum": 1
            },
            "size": {
                "$avg": "$changelog_total"
            },
            "_ids": {
                "$push": "$_id"
            }
        }
    },
    {
        "$limit": 5
    },
    {
        "$project": {
            "y-axis": "$y-axis",
            "size": "$size",
            "_ids": "$_ids",
            "x-axis": "$_id",
            "_id": 0
        }
    }
]

Full example (see also index.html):

<!DOCTYPE html>
<html>
<head>
    <title>QueryBuilder Test</title>
</head>
<body>
 
<script type="text/javascript" src="./dist/mongodb-querybuilder.js"></script>
 
<script>
// create query builder and point it to a mongodb instance and namespace
var builder = new QueryBuilder({seed: "mongodb://localhost:10000", namespace: "xgen.jira"});
 
// call .match, .group, .agg and .limit functions as much as you want
// QueryBuilder will store the state according to the given "slot" (first), overwrite
// old values, and delete the slot if you pass in a value of null.
 
builder
    .match("fields.reporter.name", ["thomasr", "ramon.fernandez", "spencer"])
    .match("fields.components.name", ["Security", "Sharding"]) 
    .match("changelog.total", [0, 50])  // for numbers, specify min and max as array
 
    // --- either use .group() / .agg()
 
    .group("x-axis", ["fields.fixVersions.name", "fields.status"])
        .agg("y-axis", "$sum", 1)
        .agg("size", "$avg", "changelog.total")
        .agg("_ids", "$push", "_id")
 
    // --- or .pick(), but not both.
 
    // .pick("x-axis", "fields.fixVersions.name")
    // .pick("y-axis", "changelog.total")
    // .pick("color", "fields.components.name")
 
    .sort("size", -1)
    .limit(5);
 
// finally, call .end to send the aggregation pipeline and return the data
builder.end(function (err, res) {
    if (err) return console.log("ERROR", err);
    console.log("DATA", JSON.stringify(res, null, '\t'));
});
</script> 
 
</body>
</html>

API

QueryBuilder(options)

Constructor to create a new QueryBuilder. Takes an options hash. The options are:

type	values
scope	hostname/port of mongoscope, passed on to mongoscope-client
seed	hostname/port of MongoDB database, passed on to mongoscope-client
namespace	namespace to query against, in `database.collection` format
samples	number of samples to create schema from

Example

var builder = new QueryBuilder({
    scope: "http://localhost:29017",
    seed:  "mongodb://localhost:27017",
    namespace: "foo.bar",
    samples: 500
});

match(field, value)

Specify a filter to be matched when querying for documents. This turns into the $match aggregation stage. The value parameter is interpreted differently depending on the type of field:

type	values
boolean	`value` is expected to be a single value, either `true` or `false`
number	`value` is expected to be an array of 2 values [`min`, `max`]. If either of the values is `null` or `undefined`, the range is considered open on that side.
date	Same as number. Both `Date()` objects and strings can be provided.
category	`value` is expected to be an array of possible values. If only one `value` is provided, the match is an equality match, if two or more values are provided, the stage is using `$in` to find the matches.

If a .match() call on the same field is repeated, the value of that field is overwritten. Specifying a value of null or undefined removes the filter on this field.

If multiple .match() filters on different fields are specified, the resulting documents have to match all filters.

Example

builder
    .match("user.lastName", ["Smith", "Miller", "Jones"])      // match users with these last names
    .match("user.age", [18, 36])                               // match users with age between 18 and 36 (inclusive)
    .match("user.created_date", ["04/16/2014", "05/31/2014"])  // match users created between these dates

group(name, field)

Group documents by their value of field and projects this value to a new field named name. MongoDB combines grouping and aggregating (or "rolling up" values) into a single $group stage. Therefore, a call to .group() is usually followed by one or more calls to .agg().

Example

builder
    .group("zip", "user.address.zip_code")
        .agg("count", "$sum", 1)
        .agg("average_age", "$avg", "user.age")

This example groups all documents by their user.address.zip_code field, and renames the field to zip in the process. For each group, the total number of documents is calculated as count and the average age of the users is calculated as average_age. The result could look like this:

[
    {
        "zip": 10009, 
        "count": 1443,
        "average_age": 27.84
    }, 
    {
        "zip": 10035, 
        "count": 2091,
        "average_age": 33.20
    }, 
    ...
]

Multi-Field Groups

You can also group on multiple fields at once. The syntax is the same as above, but instead of a single field value, specify an array of values. The resulting groups cover all combinations of the compound group key.

Due to a limitation of the aggregation framework around dot-notation keys (they can only appear at the top level), all dots are replaced with underscores in the resulting compound group key (e.g. user_address_zip_code instead of user.address.zip_code).

Example

builder
    .group("key", ["user.address.zip_code", "user.gender"])
        .agg("count", "$sum", 1)
        .agg("average_age", "$avg", "user.age")

[
    {
        "count": 23,
        "average_age": 24.3,
        "key": {
            "user_address_zip_code": 18220,
            "user_gender": "male"
        }
    },
    {
        "count": 19,
        "average_age": 25.1,
        "key": {
            "user_address_zip_code": 18220,
            "user_gender": "female"
        }
    },
    ...

Nested Groups

Example

builder
    .group("gender", "user.gender")
        .agg("total", "$sum", "count")
        .group("lastname", "user.name.last")
            .agg("count", "$sum", 1)
            .agg("average_age", "$avg", "user.age")