All right, how's everyone doing? Feeling a little refreshed after lunch? So this is NoSQL aggregation. So before I get started, just a couple of comments. It's hard for me to see out there, so if you have any questions, I might not see your hand, so just shout out.

Also, I've been told that I speak very quickly for an American recently, so if I speak a little too quickly, and English isn't your first language I realize necessarily so, please feel free to just ask me to slow down. So a quick show of hands, just to get a sense of the room.

How many people are still using relational databases? Wow, that many. How many people are using NoSQL databases? How many people are at least aware of NoSQL databases? Okay, so just to kind of set the tone, this talk is, I'll introduce some of the NoSQL concepts,

so if you're kind of new to NoSQL, in particular document databases, I'll cover the very basics of it, but it won't be sort of from the ground up, it'll just be here's everything you kind of need to research further to get to the aggregation stuff.

So a little bit about me. My name is John Zablocki, I'm the director of IT for a company called EF High School Exchange Year, out of Boston. I have a blog at dllhell.net. I'm soon going to be launching a blog on NoSQL at nosqlu.com, short for NoSQL University.

I wrote a book. You can get my code on github.com slash jzablocki. On Twitter, I'm jczablocki, and if anyone has any questions after this, you can go to about.me slash johnzablocki, and feel free to drop me a line. I'm happy to answer any questions or follow up on anything that we didn't get to cover in this talk.

Quick look at the agenda. So like I said, we'll start off with some of the basics, remind ourselves what aggregation is in SQL, which everyone here presumably knows how to do things with SQL aggregation clauses and operators and things. We'll talk a little bit about the basics of document stores, and then we're gonna spend some time on MapReduce.

How many people in the room have used MapReduce before? How many people who haven't used it have heard of it? So if I say MapReduce, what's the first thing you guys generally think of? What was that? RavenDB? Hadoop, anyone? So Hadoop tends to be the big database that everyone associates with MapReduce.

That's for kind of big jobs. So we're gonna focus more on sort of real-time MapReduce and real-time aggregation. And then we'll spend most of the time talking about MongoDB and its aggregation frameworks. And then depending on where we are with time, Couchbase will be covered too. Has anyone heard of Couchbase?

A few people. I used to work for Couchbase, but I also am a Mongo user, so I'm kind of impartial. The other thing I'll mention too is that my assumption in the room isn't that I'm here to sell you on NoSQL.

I'm kind of hoping most people have already bought into the idea. A few years ago when I started giving NoSQL talks, it was more let me convince you how NoSQL can make your job easier. I'm more than happy to have that conversation with you after the talk, but hopefully most of you have sort of gotten over the fear of a non-relational database and you're ready to move forward

with something like Mongo or Couchbase. So SQL aggregation is pretty straightforward. We've all been doing it for years. Probably some of us more, like myself, more years than I care to remember. You know, if I asked you to count the rows in a table, it's pretty easy to do. Select count from table name.

Grouping is similarly just as easy. Select column, comma, count of something, and then group by that column. Now these are pretty easy concepts, largely because SQL and relational databases kind of grew up together to perform these tasks. Now it's a little different when we start getting into the document model.

Document databases were designed for performance, efficiency, flexibility, and all these things, and then over the years, until recently, it felt like aggregation was kind of bolted on. When we look at the new MongoDB aggregation framework, I think you'll see that a lot of thought has gone into that, and it's really a pretty powerful framework that you can more or less achieve everything

you would with a SQL database. So a little bit about document stores, if you haven't worked with one. So there are three big document databases out there right now. MongoDB is by far the largest. CouchDB is a very popular open source document database,

and then we have, obviously, Couchbase. So those three databases all are more or less supporting a similar set of features, but there's some big differences, which again, find me after. I'm happy to talk to you about any use cases you might have or questions you might have about which database might fit your needs best.

In each of those cases, documents are stored as JSON or BSON in the case of MongoDB, which is binary JSON. Is everyone familiar with JSON? So documents have implicit schemas. So this is not to say that a table has a schema.

It has a set of columns. Every row in the table has the same columns. Each document itself has a set of properties and values, and that schema is implicit to that document, but it has no impact on other documents in the database. So there's no imposed schema at the database level like there is with a relational database.

There's no referential integrity. This is one of those things that people generally, when they first hear about NoSQL, they say, no way, there's no referential integrity. But something to keep in mind is that as you learn to design documents, that becomes less important. I mean, certainly there are times where you have an unnatural foreign key in the database, but a lot of times it's just the way you design your documents. You can kind of avoid the problems

associated with referential integrity not being there. And probably the most important feature of working with a NoSQL database is that you denormalize first. When we design a SQL database, what's the first thing we do? We normalize the heck out of it. And then what happens when it doesn't perform? We try indexes. What happens when the indexes don't work?

We denormalize the data into a denormalized table. So it's one of these things that when we work with NoSQL, people get uncomfortable with the idea of a denormalized data model. But again, I'm sure, how many people in this room have not denormalized a table or a set of tables? Like I saw one hand.

So if you're uncomfortable with denormalization, just remember you're already doing it just with a database that wasn't designed to support it. So let's take a look at a very simple JSON document. And for the rest of the talk, I'll work with a very simple model where we have work items and tasks

associated with those work items. So you can think of it as like a PBI and it's tasks if you're into Scrum. But you have work items, which is kind of the master thing that needs to be done. And then tasks are the individual assignments that each developer needs to work on. So in the case here, we have the work item

which has a description, an owner, and an effort. It has a primary key. So the primary key of this document exists at the root level. And then we have a nested collection. So if this were a SQL database, we'd have a work items table and then we'd have a tasks table that has a foreign key back to work items.

So I think everyone should be comfortable with that notion. And this is one of those things where you start to get away from the need for referential integrity because you can nest things in a much more natural way. So the parent-child relationship is all part of the same document. Now this won't always be the case when you work with a NoSQL database, but often the right answer is

to put related items together. All right, before I move on to MapReduce, are there any questions? So just remind me again, how many people have used MapReduce before? So just a few. So key to understanding how you aggregate data

with a NoSQL database is MapReduce. When we see the aggregation framework in Mongo, they've kind of taken it a step further and made something much more powerful. But generally speaking, MapReduce will work across the three big document databases and the same concepts kind of all come together

in each of those systems. So a little bit about MapReduce. So the idea was inspired by functional programming languages using the higher-order functions map and fold. A higher-order function in functional terms is a function that either produces a function as output

or takes a function as input. In the case of map, you write a map function that is applied to each element in a collection or in a sequence. And fold is basically just the idea of taking some recursive data structure and combining it. So if you think of like a tree with keys and values,

you basically group all the keys together and do some sort of aggregation on the values. Now that's just the sort of basic functional programming definition. Are there any functional programmers in the room? Couple, F sharp? I'm not a functional programmer, so I don't have much to add to that,

but I'm always curious. So now when we kind of expand the idea of MapReduce to a NoSQL database system, the MapReduce approach is taken to sort of a distributed level where you have a model for processing very large sets of data

across a cluster with many nodes, potentially. So the big advantage here is that you can do certain tasks in parallel. So the map function, and this is not necessarily 100% accurate across all the systems, but generally speaking, you have a master node who accepts the MapReduce job,

and it'll take the map function, spread it across the nodes each process some set of the sequence, get the results, send it back to the master node, and then the master node is responsible for running the reduce. So you distribute the work across a bunch of nodes

in the system, send it back to the master, the master does the combination. So to give you a more concrete example of what MapReduce looks like on a JSON document, here's a very simple set of documents. So I took the work items and took them down to just three properties each.

So each of our work items has a description, an owner, and an effort. So three very simple documents. The first thing you do when you write a map function is in this, the emit, so most map functions when you work with a NoSQL database will be written in JavaScript.

So when you write the map function, Mongo and the Couch databases all have this emit method. So what you do is you emit a key and a value pair. So if you want to think of this as a SQL query, this is like saying select owner comma effort from work items.

The argument on the left is the key, the argument on the right is the value. So what this looks like when you actually run that map function is you take from these documents select owner comma effort and now I have a set of key value pairs where the key is the owner name

and the value is the effort. Is everyone okay so far? So now what I want to do is in SQL terms do a group by owner where I sum the values. So again we're gonna use the reduce which is effectively select owner comma sum effort

from work items, group by owner. So the reduce function takes those keys and values as input and then in this case I'm just very simply iterating over the values which iterating over the values,

that should say values not result. I changed the slide so it should be four var i in values. Iterating over the values and keeping track of the sum. So without grouping them I get the sum of all of them and depending on which database you're using

the group happens in different ways but if I were to group it you would see that John has 13 and Molly has 34. So this is not indicative of any particular NoSQL database but it's kind of like a high level view of how it works. Most reduction in the higher level,

I mean most reductions in NoSQL databases happen with built in functions. In Couchbase and Mongo they have some helper functions that kind of do this stuff for you. So writing your own reduce isn't something you may necessarily do. And just to give you a quick example of MapReduce,

I mean I'm presuming everyone here is a .NET developer and has used Link. So if you wanted to do MapReduce on a data set in Link, this is how you implement it. Select, anytime you call .select that's effectively a map function and when you call .aggregate that is a reduce function.

So Link has full support for map and fold or map and reduce and then it's done through these different extensions. All right, any questions before I move on to Mongo? So show me again how many people have used Mongo?

So Mongo is like I said by far the most popular of the document databases. It's been around for a while and I think for a long time most people kind of associated NoSQL with Mongo. They've done a good job at that. I've been using Mongo for probably four or five years now

in various forms and over time it's gotten much closer to feeling like a relational database. Not necessarily with all the relational features but in terms of query capabilities and just the way you organize your data it feels very relational friendly. So if you're looking for what database should I jump into first to appreciate NoSQL?

Mongo is probably the easiest one to kind of get your head around as a relational developer. So Mongo is an open source NoSQL database. The company that produces it is called MongoDB. Now it used to be called TenGen.

So you may find references online to TenGen but now MongoDB is the company. It's a pure document store database. So CouchDB and MongoDB are both pure document stores. Everything is a document versus Couchbase which is a hybrid of key value pair and document database.

Records are stored as BSON, binary JSON but effectively when you're working with it you can nine times out of 10 if not slightly higher think of it as JSON. Documents belong to collections. So you store your documents in a collection which is sort of like storing it in a table but the difference is that you can have a customer's collection that has a customer object

and a product object. You wouldn't want to do that. And generally in your application layer you prevent that but collections don't impose a schema. They're just kind of a logical container. And all the CRUD operations that you perform in Mongo are done on those collections.

So if you're using Mongo, is anyone in the room using RoboMongo? If you're using Mongo and you're not using RoboMongo just stop and start it because it makes a world of difference not having to use the shell.

So RoboMongo is kind of like having SQL Server Management Studio for Mongo. It's a free open source project. It runs on Linux, Mac and Windows. It's incredibly useful especially if you're presenting large JSON queries which may not be your use case but it's a very useful tool just for visualizing your database and your documents.

So I've also, all of the examples I'm gonna show you in this talk are available on GitHub along with the sample data. So if you wanna take the data, play with it and run these queries yourself just go to gist.github.com slash J Zablocki and I have everything out there.

So I'm gonna start off by showing you I have a collection called work items. Can everyone see that okay? The one thing RoboMongo doesn't do is let you change the font size. So hopefully everyone can see that okay. If not I can show it in GitHub and make it bigger.

So I have a collection of work items like I showed you in the beginning and for each work item you'll see that there's a description, owner, effort is complete and then there's a collection of tasks.

So each work item has a collection of tasks and each task has a description, a time remaining, an assignee and a due date. We're not gonna care about all these properties but what I wanna start to show you is how you ask questions about data like this using a non-relational database.

So Mongo is pretty flexible with its aggregation capabilities. On the collections themselves you have operations distinct count and group so you can run basic aggregation queries on those.

There's some limitations like for example if you're running, so if you have a Mongo cluster and you've sharded a collection across multiple nodes then you can't use these operators on those collections but the MapReduce and aggregation framework are more optimized for that.

So let me show you to save some typing I'm just gonna copy these. So let's run the first query. So it says db dot, I'm just gonna blow this up

a little on this here so everyone sees. Make sure everyone sees it before I run it. The first thing I'm gonna do is just run the distinct function of the work items collection and if you haven't worked with Mongo the db object there is just a part of the Mongo shell

gives you this object on which you can operate on collections and just parts of your database. So you generally just it's db object dot collection name dot collection methods. So if I run the distinct on there

you'll see that I get a collection out or I get a couple of records out where each of the work item owners is listed. So this just gives me a list of the distinct work owners very simple it's just like running a select query with a distinct operation in SQL.

I can combine that with a query to say and in this case show me the distinct owner where the effort is greater than 13. So the dollar sign GT is an operator in Mongo

for greater than there's GTE, LT, all sorts of different operators that as you dig into Mongo you'll see. So if I run that you'll see I just got Molly because the record where the owner was John had nothing greater than 13.

Is everyone okay so far? Similarly I can run the count query and can you guys see it says five up there.

So again I apologize for RoboMongo not supporting a larger font. And similarly I can also count with a query. So you see that there's one record that has a effort greater than 13.

There's also a group method. So let me, so when you use the group method you specify a key, so what am I gonna group on, a reduce function, so this is when we looked at MapReduce so what am I, so I'm already telling

the group method what to reduce on and then there's the function to do the reducing in this case I'm just counting and setting the initial count to zero. So if I run that you'll see that I got

a nice group by on owner with a count of work items. So these are the three basic collection based aggregation methods in Mongo. Generally speaking they're great for quick querying and everything but they're not nearly as flexible as MapReduce or certainly not as flexible

as the aggregation pipeline which we'll see in a couple of slides. So MapReduce in Mongo, so you provide a map function, you provide a reduce function

and then optionally you can provide an out which is going to be a collection into which you'll put the results of the MapReduce. So if you wanna query a collection after you can do that. So let me run that and show you what that looks like. So this MapReduce is basically the same

that we looked at with the examples when we were just staring at the documents before. So if I run this, this is the output of the MapReduce. You'll see it has a whole bunch of statistics about what happened and it tells me that

the collection that it was outputted to is in now work items by count. So if I refresh my collections, or sorry, work items by owner, now if I view these documents, you'll see that I have a new collection

that's been persisted that has the results of my MapReduce. Everyone okay so far? Getting a little more interesting. So now let's say, so it's conceptually pretty easy

to think of how do I aggregate at the root document, root part of the document, not thinking about the nested collection. Fortunately it's not much more complicated to get into the nested collection as long as you know the basics of JavaScript. So in this case, my map function, instead of outputting something in the root document,

I can iterate over the tasks collection and emit a key that's, so what I'm doing is iterating over the task collection, calculating for each work item the sum of the time remaining.

So again, looking at these documents, what I'm doing is for each work item, iterate over the tasks and compute the sum of each of the time remaining values.

So it's very simple JavaScript. But then after that loop, I output the description, so I'm outputting for the key, so I'm gonna group by the description and the value that I'm going to aggregate is the sum of all of those tasks,

their time remaining. So if I run this, and I'll copy this find after just to show you that it outputs to a collection,

you can see now that for each of my work items, I've grouped and summed the total time remaining. So in the resulting collection, the key is the description and the value is the sum of all of the time remaining numbers.

In a similar way, I can actually, so in all the examples I've shown you so far, I've had one emit per document. There's no rule that says you can only emit

one record per document. So if I wanted to find out, show me the time remaining per assignee, so there's owners and there's assignees, so if I want to see what is the, across all of my descriptions, across all of my work items, show me the time remaining,

how much work left for a particular assignee. Again, I iterate over the tasks, but instead of emitting something at the root of the document, I emit the assignee. So this is gonna end up putting one record into the, or outputting from the map, one record for each assignee and their time remaining for a given task.

So now, if I run this, you'll see that by assignee, I have a nice sum of everything that they have left in their time remaining.

So kinda the key to understanding MapReduce is just understanding how to unwind a document, how to look at its nested collections, its elements, and project into the map, out of the map, something that you can aggregate.

You know, I think over time you learn to sort of think of MapReduce as SQL, or in a similar way to think of your SQL queries. You know, at the end of the day, data, relational databases or non-relational databases, in some sense, are just key-value pairs, or keys and value, some set of keys with an object. All right, so JSON object is a set of properties and values.

A row and a table is a set of values associated with columns. So the mindset is kind of the same, but the tooling and the way you actually get that data is very different. So now I wanna move on to

the aggregation framework in Mongo. This is a more recent and potentially more confusing at first way of dealing with aggregation.

Now I'll admit when I first tried this out, I was calling it in my head the aggregation framework, because you end up having to write a lot of curly braces and a lot of JSON, and doing that in the shell, you know, at the command line is very tricky. But using a tool like RoboMongo, you find that it's actually pretty easy to get your head around.

So I'm gonna start with a very basic group. So we use the group operator. We specify the column on which we're going to group. So when you're using some of these operators, you'll find this dollar sign owner is basically a way of referencing back into the document

a property of that document. I'm creating a new property called count, and I'm summing, basically I'm outputting a one for each of the records, and then I'm just summing all those ones. So it's effectively just doing a count of each record.

So if I run this, you'll see that I have a count of two for Molly and a count of three for John. So that count sum, so basically what this,

just to kind of give you a sense of what that count sum one is doing. So the output of the map, or whatever's happening behind the scenes, is logically something like this. John comma one, John one, John one, John one, Molly one, Molly one.

So that count sum one is at some point doing something like that, and then just summing up each of the numbers that I outputted and then associating them with a grouped key. I can do a composite key.

So if I want to group by two things, so show me how many, so it's one for most of these, but this shows me that by owner and effort, John has two 13 efforts associated with him.

So let me show you back here, the thing I added within the ID. So again, the underscore ID in the group operator is the key on which you're grouping, and it doesn't have to be, like in this first example, it doesn't have to be a single property. You can create a projection, which is, in this case, the combination of owner and effort.

So this is basically a composite key, not composite key in the primary key sense, but in that key value pair, it's two things making up the key. Now what makes the MongoDB aggregation pipeline so powerful is that it's a pipeline.

So you can have n number of operators work, basically, so the group happens, and then I feed it to a sort, then I feed it to a match, then I feed it to a whatever. So you can start to combine operators in a sort of line-by-line way,

which you can do a lot of the stuff in MapReduce, but it's not nearly as straightforward. So in this case, I'm doing the same group that I did above, but then I'm applying a descending sort based on the count. So again, the output of each operation is fed like a pipeline into the next operation.

So if you've used PowerShell or Linux, you're probably used to some of the pipeline stuff where you output a file into something else. So if I run this,

you'll see it's the same result, it's just descending now. I can add a query, and this is also a very powerful feature, and I can do this in MapReduce as well, but again, you don't have the whole pipeline model.

So here, before I do my aggregation, I wanna filter out all records where the owner is not Molly. So find all the records for Molly, and then group it.

So that eliminated the John records. There's a projection operator, which is pretty powerful. So in this example, it's not as obvious why it's powerful, but when we start getting into some more advanced examples,

you'll see that I basically cut out all the parts of my document except for the assignee. Now when we start to ask more interesting questions through the pipeline, then we'll see why we need that.

Unwind is probably one of the more powerful, but sometimes confusing of the operators, but I also think it's one of the more necessary operators as you start to pick apart nested collections. So in this case, I just wanna show you what unwind does.

So remember our document structure. We have, for each work item, we have a nested set of tasks. If I unwind the tasks,

now for each task within a work item, I have a new record in the collection. You probably, at some point in your life, had to do a query where you joined a master detail table together and ended up with a lot of redundant data from the master. So basically, you're repeating data from the master in each row for the child.

That's what's happening here. So I'm saying, unwind this task's collection and output a set of documents where, for each task, I show just the single task, but on top of it is its data from the description, I mean, from the work item. So you can see that this appears

over and over and over again. So I'm about to start digging a little deeper, but I just wanna make sure everyone's okay before I move on. Are there any questions? All right, so now let's combine

a projection with the unwind operator. So before I unwind this, I just wanna show you the output of the projection. So one of the nice things with working with RoboMongo is it's very easy

to sort of do these step-wise. When I first started learning the aggregation framework, I was using the MongoDB shell, which is a command line interface, and it's obviously with a command line, it's a lot harder to sort of piecemeal things. So at the point of the projection,

I've outputted the description and the time remaining for each of the tasks. So I have a description, and it's time remainings. But now what I wanna do is unwind it

so that I can perform a simple aggregation on the description and sum up the time remainings. So when I unwind the tasks, so again, I projected the tasks out so that I have an array of time remainings for each task. When I unwind it, I denormalize it a bit, and I get the description repeated once

for each of the time remainings. And now what I can do on top of that is add this group operation. So what I'm gonna do here is group by the description and sum up each description's time remainings.

So that basically did a select count, select sum, or select description, comma,

sum time remaining from work items where group by description. And then if I wanted to output that to a collection, I can use the out operator.

Most of the pipeline methods can kind of go in any order. And in some cases, like there's a limit and a skip operator, and the query optimizer is smart enough to sort of put some of the operations in the right order for you. But the one thing you have to do first, or last, I'm sorry, is the out operator.

If you're going to output to a collection, it has to be the last thing you do. When would you use out?

Without out, you wouldn't have, so the question is when would you use out? So the question, it's basically if you want to compute something and have it saved in a collection that you can then access from anywhere else. So the results of the aggregation, you can run them over and over again. But it'll potentially be much more efficient to have it stored somewhere.

Yeah, so well, it could potentially be, if this is a huge collection and you're persisting a very large collection somewhere, I mean that's going to depend on how much space

and power you have available. Some of the limitations too, you can't output to a sharded collection and there's some things that'll help make you decide whether to use out or not.

Yeah, honestly the question is about whether Mongo is caching the query and being able to run it again efficiently.

I'm not 100% sure, I would imagine there's a query optimizer, I'm imagining it does something like that. So in this case, if I run it, you can see now I have my output collection. Another interesting thing you can do is

work with arrays in a little bit of a different way. So we're going to do this step by step. So the first thing I'm going to do is just unwind my tasks again. So let's start with the unwind.

So this is what we saw before, just create a record for each combination of description and task, or work item and task. So now, if I run this, you can see that

for each assignee, I've pushed the set of task descriptions into an array associated with that assignee. So push lets you kind of create a new array of something within your data to associate

with one of your properties in your document. There are other operators like push that you can use. There are a bunch of set operators that are usable within your aggregation framework.

And then here, this is similar to an example above, just to show you, you can project a little deeper. So this is similar to the map reduce, the last map reduce I showed, where I dug into the assignee. So when I project, I don't have to just project

at the root level, I can project a nested element. So if I take the group off of this to show you what this is doing, you'll see that I'm projecting the assignee with the time remaining for each of those tasks. And then I can apply the group on the assignee,

sum up the time remaining, and see the results here. Now this is a bigger one to show. So you can use variables within your pipeline, which is very nice for certain things. So in this case, I start out by unwinding my tasks,

and then I create a projection. And within the projection, I'm gonna use variables to decide whether to multiply. So the idea with this query is, let's say that your team performs great for anything that's estimated at an eight or lower, but they're a little slow on the 13s and above.

So let's really fix the estimate and say that it's one and a half times the effort if the effort's been estimated at greater than 13. So I unwind the task, project the assignee, the time remaining, so this effort colon one, that is just saying include effort.

So if you aren't using a nested, so here, task.assignee, because it's a nested element, I have to reference it with quotes. The JSON will break with a dot, so the effort, I'm just including effort from the root of the document, so I just, effort colon one will include that. If I don't care about the object ID in my results,

I just turn it off by ID zero. Now I create a new computed column called predicted effort. And the definition of this, sorry, not column, property called predicted effort. The way I predict this, or create the predicted effort

is start with a let statement. The let statement is how you define variables. So it has a vars property. So I create a variable called multiplier, and the multiplier is computed by saying, okay, the conditional operator takes an if then else, so if the effort is greater than or equal to 13,

then multiplier equals one and a half, else it equals one. So it's more or less a ternary operator. So now this variable multiplier exists here, and now here, I use the multiplier variable,

and the effort to multiply this value times the effort from the document. So multiplier will equal one if it were less than 13, multiplier will equal one and a half if it's greater than or equal to 13. So the result here is going to be

based on that computation. So let me run that and show you what it does. So now you can see predicted effort here is

19 and a half, it was 13. Where it was, where it was eight, it's still eight.

This isn't that different from the sums we were doing before, but just to show you another operator, there's the average operator, the AVG. So I can compute an average of the time remaining by grouping and using the average function.

I won't run all of these because they're more or less the same, but there's a min, there's a max, there's a first and last, and when you run the first and last,

they don't make sense if you're not sorting, because it'll be unpredictable what's first and last. So in this case, I'm sorting on time remaining. And if you want to do the equivalent of a SQL having greater than, so again, I unwind my tasks,

I project out the assignee and the time remaining, like we've done a few times, group on the assignee and the time remaining, but then I can apply a match filter, or match operation, which says, only show me records where the average is greater than 14.

So if I run this, you'll see that I've eliminated anything with an average of less than 14. If I take that away, you'll see that I got the seven and the 10 and a half back.

Question? The question is whether there's a profiler to see what the query analyzer's doing, and I can't answer that, but we do have a Mongo employee here,

so if he can answer that. So Craig Wilson up there actually wrote the MongoDB, or is writing the MongoDB driver, which when you find that out right before you talk in MongoDB, it gets a little intimidating.

Thanks. And then this is a really kind of a fun feature, I think. There's a, I mean fun and useful, there's a redact operator. So let's say you have some part of your document that you don't want, or you have a document or some part of the document that you don't want

pulled back in the pipeline, based on some condition, like a particular user. You can set a condition in the redact operator that says, in this case, if the owner is the user, then descend, which will give you the document, else prune, which won't give you the document.

So if I run this, you'll see that I got all of my records, but I didn't, oops, I got all of my records, I didn't get the Molly records back,

but if the owner is Molly, then only Molly's records come back. So it's a useful way of sort of adding a little security based on some condition in your environment.

So this goes till 1440, right? So 10 more minutes. So that's basically it for the Mongo stuff. Is everyone, any questions before I do a quick Couchbase question?

I'm sorry? So the question was whether you can, what happens when you run this against a sharded collection? The, this aggregation pipeline is, works with a sharded collections.

The first set of stuff I showed you with the collection group in distinct will not. And I think there are some limitations, I've got the details offhand, but I think there are size constraints on MapReduce versus this framework. So the docs have all that specified though.

I'm sorry? Out cannot be, you cannot out to a sharded collection. I don't, you can out to, well whenever you out, if it's existing,

I believe it replaces it. So the question is whether you can out to a different database. I don't believe that's possible as part of the operation. You'd have to do some sort of copy after.

So Couchbase is very similar to CouchDB. Let me just log in here. But Couchbase, basically the basics of Couchbase

is it started off as a hybrid of Memcache-D and CouchDB. How many people have used Memcache-D? How many people have heard of Memcache-D? How about App Fabric? So if you've used App Fabric, which is a distributed cache on Azure, it's the same idea. In fact, I think they actually support the Memcache-D protocol.

So Memcache-D originally started off as just a caching, distributed cache, and it was a text-based protocol. Some guys in California that I used to work with created a binary protocol for it and made it much more efficient. They came up with algorithms for distributing it better and actually saving it and persisting it to disk.

So Couchbase is, because it's based so heavily on Memcache-D, is lightning fast. And once version two came out a couple of years ago, CouchDB stuff got put in there. It was rewritten from Erlang to C, and now you have the ability to create secondary indexes using MapReduce. So a key value store, generally, the only way to get data out is through the key.

But now with the key value stored with document capabilities, you can write a MapReduce function to query on secondary indexes. So you have a concept of a view, and within the view, you write a map function,

and optionally a reduce function. So this is the same map function we've looked at before. So I won't go through all the details of Couchbase and its MapReduce,

but just understand that it's, I can group it, I just clicked a group button. Now if I show the results, it's grouped by the two properties, or the two owners. What I do wanna show you from Couchbase, and this is not production yet, but this is actually a pretty interesting technology. So there's something that Couchbase

is working on called Nickel. I forget what it stands for, it's N1QL, but it's a query language for Couchbase documents. So to understand Couchbase, one way it's very different than Mongo, there's no concept of a collection. Everything sits in a bucket, everything exists as a document in a bucket

without any sort of abstraction over it. Now there are advantages and disadvantages to both of those approaches. Like for Mongo with the collections, you have a nice sort of way of thinking about it in terms of how it relates to a table. But you can't do clever things with, you can't do some clever things with MapReduce that you can do with Couchbase, which is like to create master view records

in a logical way, because you can look at all your documents, not just documents in a collection. You could do the same with a collection that's a little bit bigger in Mongo, I guess, but generally, Couchbase has some nice features for how you can order your documents as the result of creating MapReduce. I won't get into that now, because we only have a few minutes left, but I want to show you how with Nickel I can,

can everyone see that okay? This is how I can use Nickel and Couchbase to count how many tasks an owner has. So this is not something you can do with the drivers,

it's not something you can do without installing like a dev version, the CBQ or whatever executable, but effectively you can see what they're building is a SQL-like language on top of their MapReduce engine. I don't know if it's actually on top of the MapReduce engine, but it's a query language that lets you write SQL-like code instead of MapReduce.

So it's interesting to see the two different approaches from the different companies. Couchbase is obviously trying to move a step towards what Cassandra, has anyone used Cassandra? So Cassandra is originally a Facebook project, now it's a very popular open source database,

or NoSQL database, and they have something called CQL, Cassandra Query Language, so they have this sort of key value-like database that has a SQL-like language on top of it, and Couchbase is obviously moving towards something similar, and as you can see the Mongo folks are kind of moving

in a very different way and kind of keeping the pure JSON feel of the database. But I honestly don't know enough about Nickel to show you more than that, but I just wanted to show you kind of what's happening. So in the last couple minutes, are there any questions?

So I would definitely recommend going out and trying Mongo if you haven't, and definitely try Couchbase, they're two great products, two great databases. I'm happy to again discuss any sort of questions you might have on that stuff. Just find me after, let me just show you

some links where you can get more info on this stuff. One thing to add with Couchbase, you do get built-in reduce functions, so you have count, sum, and stats, and stats will give you min, max, count, sum,

sum of squares, so it's kind of nice to have that built-in reduce capabilities. So in C Sharp, if you were to call these methods, this is what it looks like in Couchbase, I'm not gonna dig in too much, you just create a Couchbase client, get the view,

then you get a list of key value pairs in Mongo, and I've been told recently by Craig up there that this will be a little cleaner. So when you work with, right now, in the current version of the MongoDB driver, when you work with all this aggregation stuff,

you work heavily in BSON documents, and it's a little confusing when you're in Visual Studio and seeing lots of curly braces, but that should be hopefully a little easier in the future. So where you can get all this stuff, again, gist.github.com slash Jsablocky,

that has the sample documents, the sample queries, everything that I showed you today is there, and this presentation you can grab on oneDrive.ms, lowercase L-M, capital M, capital Q, lowercase J-L-R. So this presentation is there, you can grab it, I mean, everything that's in the presentation is generally in the samples, too, so.

And that's it, thank you guys.