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.
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.
This will provide the 5 slowest interactions that a user has had for a given value of action
.