Grouping and Aggregations on the GraphQL API

Sebastian (00:03)
Hey everyone, this is Sebastian. Today we're going to be looking at grouping and aggregations on the GraphQL API. To jump into this, we're going to first look at a very simple data model that's been set up in a workspace which will guide the way that we explore the GraphQL group and aggregations feature.

Sebastian (00:19)
Essentially here in Data Builder, I have a campaigns table and an invoices table. The campaign table is pretty simple. There's a name, a start date and end date, an advertised discount as well as a relationship to the invoices table and the manager table. We can think of a campaign in any sense of the word that we want to think about. It doesn't really matter.

Sebastian (00:39)
We then have the invoices table which belongs to a campaign and has an order number and an order value. Now, we've preceded this database with a number of records, 152 records in here for all these different invoices. They belong to campaigns as well as different campaigns. I'm just going to read off a few of these.

Sebastian (01:07)
For example, 20 days off on Mother's Day, 50% off on Friday, so on and so forth. With this data, we're going to start playing with the grouping and aggregations API. I'm going to go over here now to my API Explorer, where first off, what we're going to do is just write a query where we get our data, some of the data out of the system or back from the API.

Sebastian (01:31)
Here, let's say that, hey, we want to get back our campaigns list. We want from our campaigns list the idea of the record, the name of the record, start date and end date. Cool. All right. Let's imagine that we are building a dashboard for our campaigns and inside of that dashboard, we're going to actually want to show some reporting on how campaigns are going, things like what's the average order value or things like how much revenue a campaign has generated.

Sebastian (02:04)
Now, in those situations, there are some ways that you might approach that problem, which would work with a very small amount of data. But as your data starts to scale up, it's not going to be so great. The first way would be, hey, let's actually just get back all the invoices that belong to each one. We want the ID of the invoice, we want the order value and we want the order number.

Sebastian (02:32)
Then in the front end, where we're all developing at, we would process all those records and perform the type of the aggregation functions that we want on them. Once again, for 150 records, you can get away with that but what if you have tens of thousands of records in your database or hundreds of thousands, millions of records in your database, you're not going to be able to load all that data into the front end and do that approach.

Sebastian (02:54)
The next way that you might do it is by trying to develop a custom function which loads it on the server side, processes it there and then returns it. Once again though, that will be slow as things grow and not really ideal. From there, you might even do something like maybe you have a background job that's constantly running and generating the reports and then you just query the in report.

Sebastian (03:15)
However, grouping aggregations are going to absolve our need to explore any of those solutions. We can actually just query that data directly from the database. Here we're going to look at how. First thing that we're going to do is, we will know that for any campaign, we want things like the average value for the invoice and the total revenue generated by the campaign so really we actually want to aggregate data that is stored in the invoices table.

Sebastian (03:46)
Here what we're going to do is, we're going to write a query where we get an invoice list and in that we are going to pass a group by argument. This is essentially the group in aggregation argument where we're going to define what we're going to be doing. Now inside of there, we need to make a query and we are going to be grouping by campaign.

Sebastian (04:11)
Essentially we don't want the average of invoices across campaigns, even though we could do that, we want it grouped by campaign. The first thing that we're going to do is we're going to say campaign and we are going to group by the name of the campaign, which we actually now have to use this as key and give it an alias.

Sebastian (04:35)
Now in a GraphQL API, you have...GraphQL APIs are typed. Essentially on any GraphQL API there is a defined schema for the responses and the inputs and that defines the key names that can be returned, as well as the values that are...The should be data types for those key names. Here though, we're going to use something called loose typing, which is essentially we are defining those response key value pairs on the fly.

Sebastian (05:07)
Here we're going to say that we have a campaign name and that's going to be called campaign name. Then we also are going to have the campaign ID right there, which is going to be called campaign ID. What's important is that if name and ID were two different things, they do two different groupings but we know that it will group on those two things and then it will just be a unique campaign so that's good.

Sebastian (05:42)
Then finally we can look at the manager of that campaign and pull out their e-mail address, which we are going to call the manager e-mail. Awesome. That's what we're going to group by. We're going to group by the campaign and that's the information from the campaign that we're pulling out. We're then after that going to say which aggregation functions do we want to perform on the data?

Sebastian (06:07)
The first field that we're going to perform an aggregation on is the order value. Actually that's the only field because we're just going to do it in two different ways. Since we're doing two different types of aggregations, both a sum and an average, we are going to pass an array with two different objects.

Sebastian (06:24)
First off, we're going to name it like we've been doing and this one's going to be the average order value. The function that we're going to specify for it is an aggregate function which is going to take the average. Now as you could see there's a lot of...Let me just do that, there's a lot of different aggregators that you can leverage, as well as a lot of different functions that you can leverage.

Sebastian (06:52)
A lot of depth that you can explore and it's part of the API. We're just going to look at these two more generic use cases right now. Here we're going to say average and that's our first aggregator and the second one that we're going to do is going to be the total revenue.

Sebastian (07:07)
Here we're going to name that the total revenue and we are going to specify a function which is an aggregate function and that's going to be a sum. Cool. Now what we're going to have to do is, we're going to have to define those loose typings. Let's open up the group's response object where if we try to use autocomplete here, it's not going to work because it doesn't know these essentially alias names that we are going to be using.

Sebastian (07:40)
We have to explicitly specify them and explicitly specify the type of data that we are expecting to response. For example, with the campaign ID, I'm going to say that this is an ID type field, which it will be. With the campaign name, I'm going to specify that this is a string. Manager e-mail, it's also going to be a string. The average order value is going to be a float and the total revenue is also going to be a float.

Sebastian (08:21)
Now instead of us having to have written any JavaScript with a reducer or do anything with all the records, it's actually going to perform the calculation of the database level and return us very quickly a response that has the insights that we were looking for. We can see across these campaigns the average order value, the total revenue, as well as the different fields that we pulled in from different tables on the single response objects. Pretty cool.

Sebastian (08:52)
Now the next thing that we're going to do is, let's say that you actually did in this use case want also to include the underlying data. Once again, you may want to turn this part on and off depending on how many records you have but it can make sense that, hey you still want the data of each invoice that was using the calculation.

Sebastian (09:13)
What we're able to do there is we can say that we want to get a group back and that group is going to be called invoices because that's what they are. No need for a fancy name. Now we're going to add that to our total response and then there's a custom data type in here called invoice group which we can then open a response on and we want to know for each invoice group how many records are in it and then for each record in it go back to essentially the query interface that we already see using.

Sebastian (09:59)
We want the idea of the invoice, we want the order value of the invoice and we want the order number of the invoice. Now if I run that what we can see is all that data is now coming back in the response. Under each campaign not only am I getting my aggregated data insights but I'm also getting the invoices that were used to determine that insight or calculate that insight.

Sebastian (10:22)
I hope that this gave you some cool ideas of ways that you can actually explore using grouping aggregations in the applications that you're building whether that's for reporting or any other use case it might be. Very often to actually see developers trying to do work around to get these types of insights into their application or make these types of calculations on the data store in their database.

Sebastian (10:45)
By doing some type of customer implementation which just really isn't necessary API can help you handle it in a very efficient manner and a various available manner at that too. If you find these videos helpful please hit the subscribe button so you'll be updated for future videos or future videos are released. Happy developing and looking forward to seeing you in subsequent episodes.

‍