Aggregation API

overview the aggregate api offers a series of functions to execute aggregations on a table, rather than utilising the table api whilst the table api will still be used to get information from records, this api focuses much more on aggregate functions to make sure it is much more efficient and easier to use for specific purposes tables can be accessed by using this generic notation aggregate('user') where ‘user’ can be replaced with any table name alternatively it can be accessed by the table api as well table('user') getaggregate() querying data aggregate operators the following table is a list of the supported queries for filtering, see table api operators count() the simplest operator implementing to count records for example aggregate("user") count() count(fieldname) count the non null occurrences of a field aggregate("user") count("email") distinct(fieldname) performs a count of the unique values (e g the count of the unique companies referenced from the user table) aggregate("user") distinct("company") sum(fieldname) count the non null occurrences of a field aggregate("user") sum("failedloginattempts") min(fieldname) minimum of a field values aggregate("user") min("failedloginattempts") max(fieldname) maximum of a field values aggregate("user") max("failedloginattempts") avg(fieldname) compute the average o field values aggregate("user") avg("failedloginattempts") having( criterion (“aggregate name“, value ) ) allows aggregates to be filtered from the results aggregate("trigger") group by("triggerwhen", as("when")) count("triggerwhen", as("total")) // this is the total column having(gt("total", 60)) // allow groups with 'total' greater than 60 fetch(); aggregations can be aliased aggregate("user") count(as("usercount");aggregate("user") count("email", as("emailcount")) aggregations can leverage the query operators from the table api aggregate("user") count() equal("active", true)aggregate("user") count() and( equal("active", true), gt("failedloginattempts", 0)) grouping you can add group by fields to the aggregate aggregate("trigger") in("table", \["incident", "itsmrequest"]) group by("table") group by("triggerwhen", as("when")) count("triggerwhen", as("total")) fetch(); results in \[ { "tablegroup" "itsmrequest", "tablegroupdisplayvalue" "itsm request", "total" 2, "when" "after", "whendisplayvalue" "after" }, { "tablegroup" "incident", "total" 6, "when" "before", "whendisplayvalue" "before" }, { "tablegroup" "itsmrequest", "tablegroupdisplayvalue" "itsm request", "total" 5, "when" "before", "whendisplayvalue" "before" }, { "tablegroup" "incident", "total" 4, "when" "after", "whendisplayvalue" "after" } ] querying example let aggregateresult = aggregate('sla') count(as('breached')) sum("targetduration") equal('progresslevel', 'breached'); fetchsingle(); // {breached=4, targetdurationsum=115320000} aggregateresult; // 4 aggregateresult breached;