Group By and Query Functions

The Group By statement is often used with aggregate functions (count, min, max, sum, avg) to group the result-set by one or more columns. Onyx Database supports grouping by one or more attributes or derived attributes.

Both Kotlin and Java support this feature. This tutorial demonstrates how to use group by and query functions within Onyx Database.

Steps to Use Group By and Query Functions (Onyx Cloud Database)

1

Define a Query with Criteria

Use the query builder to define a query with criteria. In this example, we filter for stats where rushingYards is not equal to zero.
1const results = await db.from('Stats')
2    .where(neq("rushingYards", 0));
2

Add a Group By Attribute

The groupBy method allows grouping by one or more fields. Fields can also be derived, such as replace("myAttribute", "\\s+", ""). In this example, we group by rushingYards to see how many players have specific rushing yards. We select rushingYards and count(player.playerId).
1const results = await db.from('Stats')
2    .select(count("player.playerId"), "rushingYards")
3    .where(neq("rushingYards", 0))
4    .groupBy("rushingYards")
5    .list();
  • The groupBy method takes one or more attribute names as strings.
  • Aggregate functions are specified using helper functions in Kotlin.
  • Supported aggregate functions include count, min, max, avg, sum.
  • Other functions like replace, substring can also be used.
3

Execute the Query

Use the executeQuery method to retrieve the results. This will return a List<Map<String, Any>>.
1results.forEach(row => {
2    console.log(`There were ${row["count(player.playerId)"]} players with ${row["rushingYards"]} rushing yards`);
3});
Note: Aliases are not supported yet.
4

Query Functions Without Group By

You can also use query functions without groupBy. For example, to get the leading rusher using the max() function.
1const result = await db.select(max("rushingYards"), "player.firstName", "player.lastName")
2    .from('Stats')
3    .where(neq("rushingYards", 0))
4    .firstOrNull();
5
6console.log(`The max rushing yards was ${result?.get("max(rushingYards)")} for ${result?.get("player.firstName")} ${result?.get("player.lastName")}`);
Next Tutorial: Partitioning

Important Notes

  • Aggregate functions like count, min, max, avg, sum can be used in selections and group by clauses.
  • The distinct() modifier can be used with count, replace, and substring functions.
  • Aliases are not supported yet, so the keys in the result maps will be the function expressions as strings.

Troubleshooting

  • Incorrect Results: Ensure that your group by fields and selections are correctly specified.
  • Function Not Recognized: Verify that you are using supported functions and that they are correctly spelled.
  • Type Mismatch: Ensure that the data types of your fields are compatible with the functions you are using.

Next Steps

Now that you have learned how to use group by and query functions, you can explore more advanced topics: