Aggregation in restdb.io queries
Lets say that you've created a data collection with Players. And you have scores and categories on each player. With a simple query you can search, group and aggregate data.The example below shows how to get all players with a score > 900, grouped by their category with the average score in each category:
https://mydb.restdb.io/rest/players?h={"$groupby":["category"],"$aggregate": ["AVG:score"]}&q={"score":{"$gt": 900}} The query above would return a JSON structure like this:
{
"doloribus": {
"score": 981
},
"explicabo": {
"score": 942.5
},
"possimus": {
"score": 937
},
"et": {
"score": 964.6
},
"accusantium": {
"score": 953
},
...
The following table shows the new aggregation functions available in restdb.io. They are appended to restdb.io REST queries in the "h" (hint) parameter as follows.
| Function | Format | Comment | Example |
|---|---|---|---|
| Min | $MIN:field | Returns object | h={"$aggregate":["MIN:score"]} |
| Max | $MAX:field | Returns object | h={"$aggregate":["MAX:score"]} |
| Avg | $AVG:field | Returns value | h={"$aggregate":["AVG:score"]} |
| Sum | $SUM:field | Returns value | h={"$aggregate":["SUM:score"]} |
| Count | $COUNT:property | Returns value with chosen property name | h={"$aggregate":["COUNT:nplayers"]} |
| Groupby | $groupby: ["field", ...] | Returns "groupkey":[array] | h={"$groupby":["category"]} |
| Groupby (dates) | $groupby: ["$YEAR:field", ...] | Predefined values for: $YEAR, $MONTH, $DAY, $HOUR, $SEC | h={"$groupby":["$YEAR:registered"]} |
| Groupby (dates with formats) | $groupby: ["$DATE:format", ...] | Format strings for: ss, hh, mm, dd, MM, YY. All formats at momentjs.com | h={"$groupby":["$DATE.MMM:registered"]} |
| Grand totals | $aggregate-grand-total: true | Recursive aggregation functions of groups | h={"$groupby":["category"], "$aggregate": ["AVG:score"], "$aggregate-grand-total": true} |
For examples and more, continue reading in our docs section.
