Skip to content

top - Return the first N rows according to an ordering expression

The top command is an aggregation command that will limit the data returned to the first N rows.

top without grouping

Without any grouping, the top keyword will return the first N values for the entire set of returned rows, according to some ordering expression.

Syntax

top <limit> <result_expression1> [as <alias>] [, <result_expression2> [as <alias2>], ...] by <orderby_expression> [as <alias>]

Example - Get the most active usernames by activity

Consider the following documents:

{
   "user": "Ariel",
   "action": "login",
   "time_taken_ms": 50
},
{
   "user": "Harel",
   "action": "logout",
   "time_taken_ms": 500
}
...

We want to understand which accounts are interacting with our platform the most. We can do this using the top keyword.

top 10 user by count()

This will return a list of the top 10 usernames by how often they appear in the logs.

top with grouping

With grouping, the top keyword limits the rows returned to a specified number, according to a specified order, and grouped by a grouping expression.

Syntax

bottom <limit> <(groupby_expression1|aggregate_function1)> [as <alias>] [, <(groupby_expression2|aggregate_function2)> [as <alias2>], ...] by <(groupby_expression1|aggregate_function1)> [as <alias>]

Example - Get the longest time taken for each action

Consider the above scenario again. Now, we want to understand the max time a given action has taken.

We can do this, using the top command with the by clause to create grouping.

top 5 time_taken_ms by action, user

This will provide the 5 slowest interactions that a user has had for a given value of action.