The following example will show how to do an Addon that returns the data by aggregate. For this example, there will be two tables: a book table containing a book title and genre and a review table containing a table reference to the book table, comments, and a rating. This example will show how to use an Addon to return the average rating of each book.
First, we will create a new Addon in the output tab of the Query all Records function for the books table. We will add the review table to the response.
Next, we will choose to return the data as an aggregate.
Step 3 has a few different options for aggregate. The first thing we must do is select how to connect the database tables.
Now we have to determine how we wish to Group, Aggregate, and Sort.
First, we will group the data by the book_id. This will organize the data by each book. We can also, customize the name.
Next, we will choose which field to aggregate the data by and which aggregator to use. There are many options to use for how to aggregate the data:
Avg - calculates the average number when dealing with data types such as integer or decimal.
Count - counts the number of values.
Count distinct - counts the number of distinct values.
Max - returns the maximum value.
Min - returns the minimum value.
Sum - adds the values together and returns a total sum.
To_list - returns an array or list of all the values.
For this example, we will aggregate the data by the rating and use the aggregator avg to calculate the average rating for each book.
Lastly, we can optionally choose how to sort the data or enable paging.
Sorting has two types:
Ascending - lowest to highest or A to Z
Descending - highest to lowest or Z to A
In this example, we won't use sorting because we are returning the average so we are just getting one value anyways.
Hit done, optionally customize the name, then confirm how the Addon is mapped, and customize the response if needed.
Now we can see in the output tab how the Addon will extend the response.
Finally, we can run this API endpoint with the Addon.