README
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")