Discussion: Adding REST Aggregates #2464
JerryNixon
started this conversation in
General
Replies: 0 comments
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
We are discussing aggregates in GraphQL and in the interest of parity with REST, I wanted to open this discussion to talk through how we might also introduce aggregates in our REST endpoints. I will start by pointing out that already copy the OData syntax for
$filter
and$select
. This has worked great. In that vein, this recommendation copies the basic OData syntax for aggregates, too.Closely related: #2461
Modeled After OData’s Syntax
Pros of Using OData Syntax
Cons of Using OData Syntax
@odata.count
).Discussion
In situations where the output response includes
"odata"
in the field name, it might make sense to simplify it by removing theodata
reference in the response. Perhaps this can be a configuration setting:runtime.rest.use-odata-response-syntax (default: false)
Sample Table
Example URL Query
Query Description
AvgAge
.Count
.Equivalent SQL Query
Example Output
URL Query Keywords
$apply
Keyword$apply
is a transformation operator in OData used to apply operations likegroupby
,aggregate
, filtering, and projections to the data.Example:
groupby
Keywordgroupby
is a transformation used to group data by one or more fields, similar to SQL'sGROUP BY
clause.Example:
$apply=groupby((country))
With multiple fields:
$apply=groupby((country, age))
Why the double parentheses?
$apply
operation. The parameters of thegroupby
function are enclosed inside these parentheses.country
), the syntax requires a second set of parentheses for consistency, allowing for multiple fields to be added if needed.aggregate
Keywordaggregate
is used to perform calculations (e.g.,sum
,average
,count
) over grouped or ungrouped data, similar to SQL's aggregate functions.Example:
aggregate(age with average as AvgAge, $count as Count)
with
Keywordwith
keyword specifies the aggregation function to apply to a field.Aggregate Functions:
average
→ Calculates the mean value.sum
→ Calculates the total sum of values.min
→ Finds the smallest value.max
→ Finds the largest value.Example:
age with average as AvgAge
$count
Keyword$count
is a special function that counts the number of records in a group.COUNT(*)
.Example:
$apply=groupby((country), aggregate($count as Count))
order by
with AggregatesExample:
$apply=groupby((country), aggregate(age with average as AvgAge))&$orderby=AvgAge desc
having
with AggregatesHAVING
clause.Example:
$apply=groupby((country), aggregate(age with average as AvgAge))&$filter=AvgAge gt 25
Comprehensive Example
URL Query:
Breakdown:
$apply=groupby((country), aggregate(...)
: Groups theusers
bycountry
and calculates the averageage
and the count.$orderby=Count desc
: Sorts the groups byCount
in descending order.$filter=AvgAge gt 25
: Filters the groups where the averageage
is greater than 25.Equivalent SQL Query:
Explicit
$count
Segment/users/$count
endpoint to retrieve only the count of matching records, without retrieving the actual data.Example:
Sample Result:
Adding Filters
You can combine
/users/$count
with query options like$filter
to count a specific subset of records.Example:
Sample Result:
$count=true
Using
$count=true
and/users/$count
serves different purposes in OData, depending on whether you need the total record count alongside actual data or only the count.Example Query:
Sample Result:
Beta Was this translation helpful? Give feedback.
All reactions