Skip to main content

Aggregate functions

J1QL offers powerful aggregate functions that allow you to perform calculations on groups of entities and derive meaningful insights from your data. These functions include SUM, AVG, MIN, MAX, and COUNT, which can be applied to numerical or date properties. By using these aggregate functions in your queries, you can efficiently summarize data, calculate totals or averages, identify minimum and maximum values, and determine the count of entities that meet specific criteria.

Supported aggregating functions (not case sensitive) in J1QL are:

  • COUNT(selector)
  • COUNT(selector.field)
  • MIN(selector.field)
  • MAX(selector.field)
  • AVG(selector.field)
  • SUM(selector.field)

Below are a few example queries implementing an aggregate function:

FIND bitbucket_team AS team
  THAT RELATES TO bitbucket_user AS user
RETURN team.name, COUNT(user)
FIND bitbucket_team AS team
  THAT RELATES TO bitbucket_user AS user
RETURN COUNT(user), AVG(user.age)

Aggregation application​

There are three different ways for aggregations to be applied:

  • On the customer's subgraph (determined by the traversal that is run).
  • On a portion of the customer's subgraph relative to a set of entities (groupings).
  • On data for a single entity.

The way aggregations happen is determined by what is requested via the query language's RETURN clause.

Aggregations relative to a subgraph​

If all selectors are aggregations, then all aggregations will be scoped to the entire traversal that the user has requested and not tied to individual entities.

Example: RETURN COUNT(acct), COUNT(user)
FIND Account AS acct
  THAT HAS User AS user
RETURN COUNT(acct), COUNT(user)

Aggregations relative to a grouping by entity attribute​

If selectors are provided that do not use an aggregation function, they will be used as a grouping key. This key will be used to apply the aggregations relative to the data chosen.

Example: RETURN acct._type, COUNT(user)
FIND Account AS acct
  THAT HAS User AS user
RETURN acct._type, COUNT(user)

Aggregations relative to a grouping by multiple attributes​

If multiple attributes of a selector are included the return function, the last one before the aggregation will be used as the grouping key.

Example: return acct._type, acct.displayName, COUNT(user)
FIND Account AS acct
  THAT HAS User AS user
RETURN acct._type, acct.displayName, COUNT(user)

Additional examples​

COUNT always returns the number of distinct entities or attributes requested. We'll illustrate an example below with the following data:

idclassnamelead
1bitbucket_teamteam1alice
2bitbucket_teamteam2bob
3bitbucket_teamteam3alice
Example query
FIND bitbucket_team AS team
RETURN COUNT(team.lead)

Our results would look like this:

Response
{
  "type": "table",
  "data": [
    { "COUNT(team.lead)": 2 },
  ]
}

Single grouping key​

For example, with the following query,

FIND bitbucket_team AS team
  THAT RELATES TO bitbucket_user AS user
RETURN team.name, COUNT(user)

the result will be:

Response
{
  "type": "table",
  "data": [
    { "team.name": "team1", "COUNT(user)": 25 },
    { "team.name": "team2", "COUNT(user)": 5 }
  ]
}

In this case, the team.name acts as the key that groups aggregations together. So COUNT(user) finds the count of users relative to each team.

Multiple grouping keys​

When there are return selectors that are not aggregating functions, the aggregating functions will be performed relative to the identifier that it is closer to in the traversal.

Example query
FIND bitbucket_project AS project
  THAT RELATES TO bitbucket_team AS team
  THAT RELATES TO bitbucket_user AS user
RETURN project.name, team.name, COUNT(user)

The COUNT(user) aggregation will be performed relative to the team, because the team traversal is closer to the user traversal in the query.

Response
{
  "type": "table",
  "data": [
    { "project.name": "JupiterOne", "team.name": "team1", "COUNT(user)": 25 },
    { "project.name": "JupiterOne", "team.name": "team2", "COUNT(user)": 5 },
    { "project.name": "Windbreaker", "team.name": "team2", "COUNT(user)": 5 }
  ]
}

If the RETURN statement is changed to this:

Example query
RETURN project.name, COUNT(user)

The COUNT(user) aggregation will be performed relative to the project.

''Response''
{
  "type": "table",
  "data": [
    { "project.name": "JupiterOne", "COUNT(user)": 50 },
    { "project.name": "Windbreaker", "COUNT(user)": 5 }
  ]
}

Example relative to a single entity​

If a selector is specified and an aggregating function is applied to that selector's source identifier in some way, aggregations will happen locally to the element.

Example:

Example query
FIND bitbucket_project AS project
  THAT RELATES TO bitbucket_team AS team
  THAT RELATES TO bitbucket_user AS user
RETURN project.name, COUNT(project.aliases), team.name, COUNT(user)

Example result:

Response
{
  "type": "table",
  "data": [
    {
      "project.name": "JupiterOne",
      "COUNT(project.aliases)": 1,
      "team.name": "team1",
      "COUNT(user)": 25
    },
    {
      "project.name": "JupiterOne",
      "COUNT(project.aliases)": 1,
      "team.name": "team2",
      "COUNT(user)": 5
    },
    {
      "project.name": "Windbreaker",
      "COUNT(project.aliases)": 5,
      "team.name": "team2",
      "COUNT(user)": 5
    }
  ]
}

Contents