Drop Aggregate

Drop data within a Flow can be grouped and aggregated in powerful ways. For example:

  • Get the average wind speed in the last 10 minutes, by town
  • Get the maximum temperature within 5 miles of New York City
  • Get the percentage of sensors that are currently online

Drop Aggregate calls can be made through both the HTTP and Track interfaces. The examples below use the HTTP interface as a convention. However, the same information can be retrieved by a Track by using the JavaScript function Flow.Drop.aggregate.

Request


POST https://api.flowthings.io/v0.1/<account_id>/drop/<flowId>/aggregate

Headers

NameDescription
Content-Typeapplication/json
X-Auth-TokenAuthentication Token

Response

HTTP Codes

CodeDescription
200Success
400Bad Request
401Unauthorized
503Service Unavailable

Flow.js

Flow.Drop.aggregate(flowId, options)

  • flowId - the string value of the flow's id or path
  • options - a JavaScript object containing the required fields below, plus any optional fields.

Required Fields


groupBy
A list of the fields by which you'd like the results grouped. Results can be grouped by any literal element within a Drop, or by Date Selector, e.g. "$month:creationDate"
output
The fields you want to output in your query. These must make use of the Selectors, e.g. "$avg:temperature"

Optional Fields


filter
For defining the parameters of the search, the filter will only include the Drops whose data you're interested in aggregating, see the Flow Filter Language documentation for more details
rules
You can create rules based on filters, for example, "x": "a > 1", or "x" : "location WITHIN 50 MILES OF [40.5,-73.5]" , ( in this case you'd refer to these fields in your groupBy or output as the named key of the field, 'x'. make sure your named fields are unique). The output of a rule will always be 1 or 0, so $avg:x will return the % that passed the rule.
sorts
The order in which the results are returned. Multiple fields can be specified. Fields must match those specified in the "groupBy" or "output" attributes.

Selectors

Functions that wrap a drop element

Dates:

For any field that is specified as a date, you can use a Date Selector to retrieve part of that date. This can be useful for grouping purposes. The selectors are:

  • $year
  • $month
  • $day
  • $hour
  • $minute
  • $second

Example: "$month:lastEditDate" will return only the month component from the date. Use this to group results by the month they were last edited.

By omitting the element name, "creationDate" will be automatically chosen. E.g. "$year" returns the year the Drop was created.

Aggregate:

Specified an aggregation function applied to a field.

  • $sum:field The sum of the field values for the defined grouping
  • $avg:field The average value of the field for the defined grouping
  • $unique:field A list of unique values for the field within the defined grouping
  • $max:field The maximum value of the field for the defined grouping
  • $min:field The minimum value of the field for the defined grouping
  • $all:field All values of the field for the defined grouping, includes duplicates
  • $count - matching count of found records.

Distance:

If you have specified a rule element that utilizes a location, e.g. x : location WITHIN 20 MILES of [123,-321], you can use the distance selector $distance:x to retrieve the distance this Drop is from [123,-321].

This can be used to sort or group the results, or as a general output.

Example


For example, if there was a Flow that contained Drops indicating the processor's temperature as a 'cpu_temp' elem, in order to obtain the average, minimum and maximum temperatures on a monthly basis:

POST https://api.flowthings.io/v0.1/bob/drop/f549495f90cf2dc116f90bb41/aggregate

{
  "filter":"EXISTS cpu_temp",
  "rules":{"x":"cpu_temp > 50"},
  "groupBy":["$month"],
  "output":["$avg:cpu_temp","$min:cpu_temp","$max:cpu_temp","$avg:x","$count"],
  "sorts":["$avg:x:desc", "$avg:cpu_temp:asc"]
}
  • "filter":"EXISTS cpu_temp" This uses a sample set which ensures that all Drops selected for this aggregate query contain a cpu_temp, all others will be excluded.
  • "rules":{"x":"cpu_temp > 50"} This specifies a rule to define how often the cpu_temp is above the ideal temperature, in this case 50.
  • "groupBy":["$month"] I want the response to provide numbers on a monthly basis.
  • "output":["$avg:cpu_temp","$min:cpu_temp","$max:cpu_temp","$avg:x","$count"] Here the maximum, minimum, and average temperature values will be returned in the grouping for each month. The last data point in the output, $avg:x, will return the percentage of Drops that pass the rule that was defined in rules. $count will return the size of the sample set of Drops that satisfied the filter within each grouping.
  • "sorts":["$avg:x:desc", "$avg:cpu_temp:asc"] The results will be sorted by the $avg:x field in descending order first, then by the $avg:cpu_temp field in ascending order.

Example Response


{
  "head" : {
    "status" : 200,
    "ok" : true,
    "messages" : [ {
      "query" : {
         "filter":"EXISTS cpu_temp",
         "rules": {"x":"cpu_temp > 50"},
         "groupBy":["$month"],
         "output":["$avg:cpu_temp","$min:cpu_temp","$max:cpu_temp","$avg:x","$count"],
         "sorts":["$avg:x:desc", "$avg:cpu_temp:asc"]}
    } ],
    "errors" : [ ],
    "references" : { }
  },
  "body" : [
    { "_id": { "month": 2, "year": 2015},
      "$avg:cpu_temp": 49.75333333333331,
      "$avg:x": 0.3333333333333333,
      "$count": 120,
      "$max:cpu_temp": 56.2,
      "$min:cpu_temp": 45.5},
    { "_id": { "month": 1, "year": 2015},
      "$avg:cpu_temp": 49.10580204778171,
      "$avg:x": 0.2832764505119454,
      "$count": 293,
      "$max:cpu_temp": 98.6,
      "$min:cpu_temp": 39.0}
  ]
}