Aggregation | MongoDB | Tutorial 10

hey welcome the drafter tattoo me my name is Mike in this tutorial I'm gonna teach you guys about aggregation in MongoDB the aggregation basically allows us to process data records and we can return computed results so essentially what we can do is we can find out a bunch of cool information about the stuff that's already inside of our collections so we can get information that's not necessarily stored in the collections but we can like do computations on that information and figure out different information so it's gonna be pretty cool aggregation is a pretty common tasks for any database so I'm gonna show you guys basically how that works in MongoDB now the first thing we're gonna do is we're gonna set up a little collection so I actually have some code here for a new database collection so it's called purchase orders and basically this is just gonna store like some different purchase orders like maybe we're setting up a database for a store obviously this is very simple but you know it's a simple example so we can learn in the concepts so over here each of these entries has the product that got sold how much it got sold for and the customer who bought it so we have like the customer name the amount that they spent on the item and then the item itself and you'll see here like some of these show up multiple times so like Mike shows up a couple times Karen shows up a couple times in the database and also different products so toothbrush shows up here a couple times Pizza shows up a couple times so we have some information that's kind of like repeated down there so I'm gonna show you guys how we can use aggregate in in order to get all sorts of information from this purchase orders collection but our first order of business is to actually create it so I'm gonna go ahead and just copy this bad boy and I'll have a link to the code in the description below but we'll put this down here and we'll go ahead and insert all that so all that stuff got inserted into the database why don't we go and check out the DB compass just to see so if we refresh this you'll see now we have our purchase orders table or a purchase orders collection and then down here we have all of our purchase orders so there's all of our items so now let's get into a grigory you know really dive into aggregation though is a couple of different MongoDB functions that we can use so these are kind of cool and I'll just show you guys a few different examples so I'm gonna give us a couple different prompts and we'll see if we can figure out how to solve them so here's a prompt it says find out how many toothbrushes were sold all right so this is kind of interesting like how can we find out the number of toothbrushes that were sold from the purchase orders table what I can come down here and I can do something like this so I can say DB purchase orders dot count so I can use this count function over here and then inside of parentheses I can put a little object and this is basically just telling the count function what field we want to counts over here I just want to count toothbrushes so products was the name of the field and we want to count all the toothbrushes from that field so we can go ahead and put that down here and now you'll see when I run this that we get back three so three toothbrushes were sold in the purchase orders table and actually let's take a look to make sure so yeah it looks like one two three toothbrushes so that was right so that's basically how we could count a specific entry in a specific field so we can count to see how many toothbrushes were sold we could also do something different so let's put up another prompt here so it says find a list of all products sold so basically we want to get a list of all the individual products that were sold but I'm assuming in this case we don't want any duplicates right so we sold three toothbrushes but we just want a list of all the products we don't necessarily want you know all of the entries inside of the purchase orders table so what we can do is we can use something called distinct so I could come down here and I'm just gonna paste this it says DB purchase orders dot distinct so distinct will return entries in the purchase orders table that are distinct and so over here we can return distinct products so inside these quotation marks I'm just putting the name of the field so we have the product field so this will give me all of the distinct products that were sold I'm gonna put this down here and you'll see we get this array so its toothbrush guitar milk and pizza so even though we sold three toothbrushes and even though we probably still in multiple pizzas as well it's only gonna give us one because we use distinct and so this is how we could just get you know a list of all the products that were sold without any of the duplicates so those are a couple of useful functions and those are two different ways that you know you can kind of information right so we can get information about the data inside of the collection but now I'm gonna talk to you guys about aggregation so here is another prompt I'm going to throw up here it says find the total amount of money spent by each customer this is kind of interesting right we have all of our purchase orders and as we saw before the same customer sometimes purchased or made different purchases so how can we find the total amount of money spent by each customer well what we can do is we can use something called aggregate so I can say DB dot purchase orders so the name of the collection and then aggregate and then over here inside of these parentheses we can put in a race we're gonna put an array here and then inside of this array we're gonna make an open and close curly bracket and I just want to say match and what this is gonna do is it's gonna act as a filter so over here I'm just saying match and then an open and close curly bracket so that's gonna match anything that'll match every entry in the collection and then down here I'm going to say group and this will group all that stuff together so this will basically aggregate everything together so here I'm gonna say ID and then over here I'll say customer and basically what this means is that the ID of the things that we get back is going to be the name of the customer so we're basically gonna group these by the customer and then total is going to be another field we're gonna get back and I'm just gonna say over here sum and sum is gonna mean that we're gonna add everything together so here I'm gonna add together all of the totals and so this is how we can aggregate something so I'm gonna copy this I'll put this down here and now when I hit enter you'll see what we get back is this little collection right here the ID of the collection that we got back is the name of the customer right and then over here we added in another field called total and this is how much the customer spent so Dave spent four dollars and 75 cents Karen spent 13 dollars and 25 cents Tom spent 199 dollars so all of that information isn't necessarily stored in its own fields in the database right if we come back over here like you'll see there's nothing over here that says that you know Karen's Bentley $13 or however much she spent we got that information by aggregating the information from the collection together so let's walk through one more time how this aggregate is set up so over here we have a filter right so this is gonna filter the different entries in the collection so we might not want to aggregate everything in the collection we can filter it in this case we weren't we were just kind of making an open and close curly bracket which won't filter it and then down here we specified how we wanted to group the information together so this might be a little bit confusing I'm gonna try to explain to you guys so over here inside of these open and closed curly brackets we're basically defining the structure of the information that we want to get back so we're essentially defining this structure down here and we're saying that we want to have a field on here called ID so the ID is going to be like what identifies each row now remember an ID is something that's unique to each entry in a collection and so the ID is something that is going to be unique to each of these entries down here so basically our goal was to group together all the information about specific customers so we wanted to get the totals that each customers spent and therefore we put customer over here in the ID field because the ID field has to be unique and so it has to be unique and we use it on the customers that means that we'll be able to group all this information together by the customer and then over here we have another total which was sum and then over here it's just the total so basically what this is going to do is it's gonna group together all of the entries in the purchase orders table that have the same customer name and it'll add up all of the totals associated with those entries and so that is basically how we get this down here so let's try another one let's see if we can just modify this a little bit how about if we wanted to get the totals of each of the products so let's say we wanted to figure out how much money was spent on each products well here instead of grouping by customer we're gonna want to group by product and then over here we can do the same thing so we don't have to change anything else instead of grouping it by how much the customer spent we can group it by how much money was sold of the particular product so let's go ahead and do that I'm just gonna enter that in down here and we hit enter and so now we have different types of information right so now instead of grouping everything together by the customer we're grouping everything together by the product so pizza there was a total of $13.25 spent on pizza there was a total of $11.33 spent on milk $14.25 was spent on the toothbrush right so that is basically how aggregation works we can specify what filled in the collection we want to group everything by so when we group everything by products then we get all the product and we view of everything by customer then we get all the customer and then obviously over here we're just adding up all the totals of those specific entries so hopefully that makes sense an aggregation sometimes is a little bit hard to wrap your head around but hopefully that example at least helps a little bit so another thing that you might want to do with aggregation is sort stuff so down here we have all the money that customers spent on the products but you'll notice that they're just kind of in random order if we wanted we could sort these in ascending or descending order so I could come down here and I'm just gonna make another open and close parentheses or curly bracket and then you'll notice I put a comma there as well so over here we can define some sorting so I could say like sort and make an open and closed curly bracket and then over here we could sort it by the total and I'm just gonna say negative one so we'll sort of in descending order and so total we defined up here as being the sum of all of the totals for each product right and so down here this will sort everything in descending order by those totals so let's go ahead and we'll copy that and we'll paste it down here and you'll see now that all of these are ordered by the totals so it's ordered in descending order so we start with the most expensive and then we go to the least expensive and I can do the same thing for customer so I could change this to customer and then if we wanted we could do exactly the same thing so now we'll order this by customer or by how much the customer spent so Tom spent the most and then Mike and then Karen and then Dave so that's basically another cool thing that we do with aggregation which is you know obviously sorting things and then obviously if we wanted we could you know filter these out a little bit more so I could put a filter in here and so instead of grabbing all of the entries in the collection we could just grab certain one so I could filter like by customer so I could say like customer and then we could grab only the customers that were in you know maybe like Mike and Karen so now what this will do is it'll only grab the information that matches the filter and we'll be able to grab that information so I'm gonna go ahead and copy this and we'll paste it down there and actually look whips lipstick I had a typo here so we need this semicolon so let's do it again I'm gonna paste this down here and you'll see now we get essentially the same results but it's only with these two customers so obviously you can just like anything else you can filter it out all right so hopefully that makes sense that's kind of the basics of you know using aggregation and like I said a grenade that we can perform different operations on the data in the collection right so we're not like actually keeping track of like the totals that you know Mike or Karen spent that information is technically like in the database so we can use aggregation in order to you know get that information organize it and then display it properly hey thanks for watching if you enjoyed the video please leave a like and subscribe to drop acad to be the first to know when we release new content also we're always looking to improve so if you have any constructive criticism or questions or anything leave a comment below finally if you're enjoying chopped Academy and you want to help us grow head over to draft Kadim e-comm forward slash contribute and invest in our future

Channel: Mike Dane

Views: 160,558

Keywords: Programming

Id: Kk6Er0c7srU

Length: 12min 44sec (764 seconds)

Published: Fri Dec 22 2017