Aggregate functions

NotoDB supports SQL-like aggregate functions. Use the query method to leverage aggregate functions in your application.

Basic syntax

The following is the basic syntax of a query request with an aggregate function.

POST
/{bucket name}/sets/{set name}/query?key}={value}
REQUEST BODY
[
  {
    "{aggregation}": "{field to aggregate or * (all)}"
  }
]
RESPONSE BODY
[
  {
    "_1": "{int result}"
  }
]

An example aggregate function

In the following example we are going to return the average age of all users who live in Oakland, CA.

POST
/mybucket/sets/users/query?city=Oakland&state=CA
REQUEST BODY
[
  {
    "avg": "age"
  }
]
RESPONSE BODY
[
  {
    "_1": 42
  }
]

Multiple aggregate functions

In the following example we are going to return the total count and average age of all users who live in Oakland, CA.

POST
/mybucket/sets/users/query?city=Oakland&state=CA
REQUEST BODY
[
  {
    "count": "*"
  },
  {
    "avg": "age"
  }
]
RESPONSE BODY
[
  {
    "_1": 379,
    "_2": 42
  }
]

Note that the response return contains keys such as _1, _2, _3, etc. These are in the order in which the aggregations are sent. In the above example, _1 is the total count, whereas _2 is the average age.

Count all items example

To return the total count of all items in a set, or run an aggregation across all items in a set without any query parameters, consider the following example:

POST
/mybucket/sets/users/query
REQUEST BODY
[
  {
    "count": "*"
  }
]
RESPONSE BODY
[
  {
    "_1": 23452
  }
]

Available functions

Aggregate function
COUNT{"count":"*"}
AVG{"avg":"age"}
SUM{"sum":"transaction_amount"}
MIN{"min":"age"}
MAX{"max":"age"}

See Querying items for more information on querying items in a set.

Next articleUpdating items