How to use Aggregate Functions in Stargate’s GraphQL API
Published: 2022-09-08 17:11:09


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i0sqlpwuvs5mh4ss9hsu.jpeg)

_The Stargate GraphQL API has gotten even better with aggregate query functions. Get started using aggregate functions in your database with this short tutorial._

Aggregate functions are a relatively new feature in the release of Stargate for [Astra DB](https://astra.dev/3RwI2DM). If you’re not familiar with aggregate functions, they are functions that look at the data as a whole and perform a function like min(), max(), sum(), count() and avg().

Until now, aggregate functions were only available using `cqlsh` (the CQL Shell). However, with the [Stargate 1.0.25](https://github.com/stargate/stargate/releases/tag/v1.0.25) release, they are now also available using the GraphQL API. In this blog entry, I’ll walk you through the process to get early access to this exciting new functionality in Stargate, and how to set up everything you need to test your own aggregate queries.

Using `cqlsh` to perform an aggregate query is pretty straightforward. Let’s assume you have an employee table with the following sales data:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fx9kpiqan64ggtpoe9io.png)

Now we want to find the highest sale number for employee 1, John. Our `cqlsh` query would look like the following:


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6wm4vjknta2as8c81aim.png)

Then it would return a single record:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2ku2nj893lx99l1suc69.png)

# Prerequisites for this tutorial

This blog tutorial assumes that you already have[ Docker](https://www.docker.com/) and[ curl](https://curl.se/) installed and configured on your machine. Alternatively, if you have an [Astra DB](https://astra.dev/3RwI2DM) account (even with the free tier free) you can do your testing there.


# Getting Stargate

If you are using Astra DB you can skip this part and go to the next section.

The main repository for the Stargate source code is on [GitHub](https://github.com/stargate/stargate).[ ](https://github.com/stargate/stargate)However, I recommend just using the Docker container that is already configured for testing and run this command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1mjtzacu9ulkmu0jwt1n.png)

Your Docker instance of Stargate will load and start executing.


# Preparing Your Test Environment

Next, we need to create our _keyspace_ and _table_. Then we will load our test table with test data. While it is possible to do almost all of this using [GraphQL](https://graphql.org/), I did most of it using the REST API since that is the API.


_Note: All of these URLs are designed for the Docker container running locally on your machine. If you are using Astra, adjust the URLs accordingly._

Once the Docker image is fully up and running, you will need to get authentication credentials for the Cassandra instance it contains. Use this curl command to get the authentication token:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uhnjwjcn4lzrn6ezifv3.png)

Set the auth token as an environment variable for easy reuse.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i3hjkj147rpvvwm6iort.png)

Now run the following command to get a list of existing keyspaces. This is a good test to ensure you’ve set your authToken environment variable correctly:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jw3rzeg13a6c03ybocaw.png)

You should see the following output from the command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vyviiitd9c4b1iribnf7.png)

Next, we need to create our keyspace for our database. The following command will create the test keyspace:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yy75cwwzr5m1rl9h761j.png)

Now we need to create our employee table in the _test_ keyspace. This command is rather lengthy for a blog post so I recommend getting the create_table.sh file from the [GitHub repository](https://github.com/jdavies/blogs/blob/master/20210602_aggregate_stargate/create_table.sh).

It’s time to load some data into our table. The easiest way to do this is to download the[ load_data.sh](https://github.com/jdavies/blogs/blob/master/20210602_aggregate_stargate/load_data.sh) file from my GitHub repository (another blog-unfriendly script) and execute it via the command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/81vopqhnkg0au90bttyd.png)

This contains the curl commands to load the records into your Docker database.

Once the data is loaded, let’s run a quick query to ensure that everything is as we expect. Execute the following command:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wkzbfv6ooo92argkwg0x.png)

You should get all nine rows of data back. They can be a little hard to read from the terminal. If you want to see a prettier version, I suggest copying the resulting text and pasting it into an online JSON browser like[ jsonbeautifier.org](https://jsonbeautifier.org/). You should see the following nine rows:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7s9wfxtuvo9kka77wgvp.png)

Now we are ready to get down to business!


# Using Aggregate Queries

Since I’m new to GraphQL, it seems strange to me. Although once you get used to its way of doing things (like omitting commas in a JSON-esque data format) it’s pretty straightforward. Here is the curl command that will retrieve the highest sales year for employee 1, named John:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/51r1oa6uoeqnq1912xwl.png)

The payload is a little hard to read on the command line, so here it is in GraphQL format:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bmhwxfokxd3n9w00bvm0.png)

If you examine the command, you will see how we included the max() aggregate command (named as “highest_sale”). Just like a cqlsh version of the call, the max() function is applied to the _return_ values, not the _select_ criteria. Your output should match the following:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ntti98oy0hzxagzjhptj.png)

How about searching for the highest sale of all time? Here’s how you do it:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/907yajb0jxen58wrqkvw.png)

By removing the “where” clause from the GraphQL statement (traditionally in the parenthesis after the table name), you can search the entire table, across all partitions.

In `cqlsh` this is the equivalent of adding `ALLOW FILTERING`, which in general is regarded as a “bad thing” since it forces a full table scan across all partitions, which can be very slow. While aggregate functions are often used for reporting, it might be acceptable to do this for a few special queries.

`ALLOW FILTERING` isn’t necessarily bad, but you have to understand what it does and use it sparingly if you want to keep your database performing at max speed. `ALLOW FILTERING` can come in very handy when we’re invoking a `SELECT` operation on a single partition (for example, providing the full partition keys at the very minimum, which is `id` in this `test.employee` table’s case).

On a final note, bear in mind this is GraphQL API-specific. It won’t work with the REST or Document APIs.

Get started with aggregate queries now on [Astra DB](https://astra.dev/3RwI2DM).

_Get up to speed faster with tutorials on our [DataStax Developers YouTube channel](https://www.youtube.com/channel/UCqA6zOSMpQ55vvguq4Y0jAg) and subscribe to our [DataStax Dev Twitter channel](https://twitter.com/DataStaxDevs) to get notified about new blogs. For exclusive posts on all things data, follow [DataStax on Medium](https://datastax.medium.com/)._

**Resources**



1. Join our Discord: [Fellowship of the (Cassandra) Rings](https://discord.com/invite/pPjPcZN)
2. [Build your first app with javascript, node js and Astra DB](https://datastax.medium.com/build-your-first-app-with-javascript-node-js-and-datastax-astra-db-573abc238583)
3. Astra DB — [Apache Cassandra as a service](https://astra.dev/3RwI2DM)
4. [The Fast Lane and the Open Data Stack](https://www.datastax.com/blog/fast-lane-and-open-data-stack) — exciting news from DataStax CEO Chet Kapoor
5. [Compiling Stargate](https://jeffmdavies.medium.com/compiling-stargate-38f70c0e8eea) — more tips from Jeff Davies