On this page

• Start Free Trial
• bottom without grouping
• bottom With grouping

bottom

The bottom keyword limits the rows returned from a query to the last N rows in a given set, according to an ordering expression.

NOTE: When using this keyword, pay close attention to how you order your data.

bottom without grouping

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

Syntax

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

Example - Get the least active usernames by activity

In this example, we have log documents representing some user engagement with our system:

{
   "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 least, so that we can remove them from our system. We can do this using the bottom keyword.

bottom 10 $d.user by count()

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

bottom With grouping

With grouping, the bottom 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 least active usernames, with an activity count and total processing time

Continuing with our example above, we now want to see a count of how many events they've performed, and we also wish to total up their processing time, since some events may taken more time than others.

bottom 10 user, count() as activity_count by sum(time_taken_ms) as total_processing_time_ms

The response from this query will be of the form:

[
   { "user": "Ariel", "activity_count": 38, total_processing_time_ms: 54000 },
   { "user": "Harel", "activity_count": 31, total_processing_time_ms: 12000 },
   ...
]

Supported aggregation functions are listed in "Aggregation Functions" section.

Previous block

Next choose