Support Aggregate Functions in GET APIs

We’ve added support for aggregate functions in our APIs. By supporting aggregate functions, we enable users to obtain summarized data directly from the API, reducing the need for additional processing. The datetz transform ensures accurate date-based analysis, especially in scenarios involving multiple timezones.

AGGREGATE FUNCTIONS FOR POWERFUL DATA SUMMARIZIATION 

Our API now supports a range of aggregate functions, allowing you to perform calculations directly within the API responses. This eliminates the need for external processing, resulting in faster and more efficient data analysis. The following aggregate functions are now available:

  • SUM: Calculate the sum of values for each specified group.
  • AVG: Determine the average value within each group.
  • MIN: Find the minimum value in each group.
  • MAX: Identify the maximum value, providing insights into the highest values in your data.
  • COUNT: Efficiently count the number of rows in each group, allowing for quick assessments of data distribution.

To utilize these functions, simply include them in the values_list query parameter of your GET requests to entity APIs.

For example: 

?values_list=count(id),sum(value)

datetz TRANSFORM - TIMEZONE-AWARE DATE TRUNCATION 

The datetz transform, allows you to truncate timestamps to dates while maintaining the critical timezone offset. This is especially useful when dealing with time-sensitive data and ensures accurate analysis across different timezones. You can apply the datetz transform directly on model filters or within the values_list of an entity GET API request.

For example: 

?values_list=create_ts__datetz

NOTE: While datetz is powerful for data retrieval, it cannot be used as a filter. Ensure that you use regular timestamps for filtering and manually adjust them to the desired date with time set to 00:00:00 in the filter request.

EXAMPLE USAGE 

Here's how you can leverage the new features to retrieve insightful data:

REQUEST: 

GET /inventory/?values_list=expiry_date,count(id),sum(curr_qty)&ordering=expiry_date

Result: This request efficiently retrieves the count of IDs and the sum of curr_qty for each expiry_date , grouping and ordering the results by the expiration date.

REQUEST: 

GET /inventory/?values_list=create_ts__datetz,count(id),sum(curr_qty)&ordering=create_ts__datetz

Result: Retrieve the count of IDs and sum of curr_qty for each day, grouped by the creation date and ordered while considering the timezone offset.

When using these features, remember the following:

  • Always include at least one additional field for grouping when using aggregate functions.
  • Specify the grouping field(s) in the ordering parameter to maintain the desired result order.

Steps to Enable

Review the REST service definition in the REST API guides to leverage (available from the Oracle Help Center > your apps service area of interest > APIs & Schema). If you are new to Oracle's REST services you may want to begin with the Quick Start section.