Glidequery Part 4 - Aggregates

#glidequery , #scripting

Peter Bell

3 minute read

Advanced Aggregate Queries

As I mentioned in my previous article, GlideQuery supports simple and complex functionality. The simple aggregate functions sum, avg, max, and min are easy to use and have little syntax. However, they have two significant limitations:

  1. They don’t support grouping
  2. They support only one aggregation per query

Grouping

What if I want to count the number of users in each city?

new GlideQuery('sys_user')
    .aggregate('count')
    .groupBy('city')
    .select()
    .forEach(function (group) {
        gs.info(g.group.city + ' has ' + g.count + ' users');
    });

// Seattle has 37 users
// San Francisco has 86 users
// ...

This requires some explanation. You’ll notice we’re using a generic aggregate function and specifying that we want to do a count aggregation. aggregate and groupBy are fluent-style methods that both return a new GlideQuery object. That behavior allows us to perform multiple aggregation queries and to group by multiple fields.

Another thing to note is that we’re using select because we’re expecting multiple objects to be returned (this time, they’re groups, not individual records). Remember, select always returns a Stream. So what’s the shape of each group in a Stream?

If we were to run this query:

new GlideQuery('sys_user')
    .aggregate('count')
    .aggregate('max', 'salary')
    .aggregate('max', 'age')
    .aggregate('avg', 'salary')
    .groupBy('city')
    .groupBy('country')
    .select()
    .forEach(doSomething);

Then we should expect to see a Stream of objects that look something like this:

// {
//     "group": {
//         "city": "Seattle",
//         "country": "US"
//     },
//     "count": 67,
//     "max": {
//         "salary": 250000,
//         "age": 62
//     }
//     "avg": {
//         "salary": 77000
//     }
// }

You can see each group object in the Stream specifies which group it falls into (in our case, city = "Seattle" and country = "US"), as well as all the aggregate values for that group. Each object in the Stream would have a different set of city/country values.

Having

SQL (and GlideQuery) supports “having” clauses, where we can filter groups.

new GlideQuery('core_company')
    .aggregate('sum', 'market_cap')
    .groupBy('country')
    .having('sum', 'market_cap', '>', 0)
    .select()
    .forEach(function (g) {
        gs.info('Total market cap of ' + g.group.country + ': ' + g.sum.market_cap);
    });

// Total market cap of Canada: 48930000000
// Total market cap of UK: 5230000000
// ...

Here we’re filtering the groups only to include countries where the total market_cap is positive. Because this query is being executed on the SQL server, it’s more performant than reading all the groups and filtering in JavaScript. Sometimes JavaScript filtering may be necessary because of a limitation in GlideAggregate, which prevents us from using having on non-numeric fields.

One Query Class

You may have noticed that unlike GlideRecord/GlideAggregate, GlideQuery is used for both aggregate and non-aggregate queries by design for two reasons:

  1. Some find it easier to use only one class for querying
  2. GlideQuery objects can be constructed and reused for both aggregate and regular queries

The first point is a matter of taste, but the second point is actually quite powerful. Take this simple example:

var activeUsers = new GlideQuery('sys_user')
    .where('active', true);

// perform aggregate query on active users

var activeUserCount = activeUsers.count();

// ...then send each active user an email

activeUsers
    .select('email')
    .forEach(sendEmail);

In the above scenario, I can reuse the same GlideQuery object for two separate queries: one aggregate, the other non-aggregate. Hopefully, even this simple example helps you understand such that You can think of scenarios where reusing queries could greatly simplify your codebase.

Conclusion

For most aggregate queries, I recommend using the simple aggregate functions min, max, avg, sum, and count. They are simpler to write and reason about. However, when grouping or multiple aggregations are needed, GlideQuery can help. It includes the usual field/type checking you’d expect in other parts of the API, and does the type conversion (for boolean/numeric fields) for you.

Comments