chart
The chart command transforms search results by applying a statistical aggregation function and optionally grouping the data by one or two fields. When grouped by two fields, the results are suitable for two-dimensional chart visualizations, with unique values in the second group key pivoted into column names.
Syntax
The chart command has the following syntax:
chart [limit=(top|bottom) <number>] [useother=<boolean>] [usenull=<boolean>] [nullstr=<string>] [otherstr=<string>] <aggregation_function> [ by <row_split> <column_split> ] | [over <row_split> ] [ by <column_split>]
Parameters
The chart command supports the following parameters.
| Parameter | Required/Optional | Description | Default |
|---|---|---|---|
<aggregation_function> | Required | The aggregation function to apply to the data. Only a single aggregation function is supported. Available functions are the aggregation functions supported by the stats command. | N/A |
<by> | Optional | Groups the results by either one field (row split) or two fields (row split and column split). The parameters limit, useother, and usenull apply to the column split. Results are returned as individual rows for each combination. | Aggregate across all documents |
over [] by [] | Optional | Alternative syntax for grouping by multiple fields. over <row_split> by <column_split> groups the results by both fields. Using over alone on one field is equivalent to by <row_split>. | N/A |
limit | Optional | The number of categories to display when using column split. limit=N or limit=topN returns the top N categories. limit=bottomN returns the bottom N categories. When the limit is exceeded, remaining categories are grouped into an OTHER category (unless useother=false). Set to 0 to show all categories without a limit. The ranking is based on the sum of aggregated values for each column category. For example, limit=top3 keeps the three categories with the highest total values. Only applies when grouping by two fields. | top10 |
useother | Optional | Controls whether to create an OTHER category for categories beyond the limit. When set to false, only the top or bottom N categories (based on limit) are shown without an OTHER category. When set to true, categories beyond the limit are grouped into an OTHER category. This parameter only applies when using column split and when there are more categories than the limit. | true |
usenull | Optional | Controls whether to group documents that have null values in the column split field into a separate NULL category. This parameter only applies to column split. Documents with null values in the row split field are ignored; only documents with non-null values in the row split field are included in the results. When usenull=false, documents with null values in the column split field are excluded from the results. When usenull=true, documents with null values in the column split field are grouped into a separate NULL category. | true |
nullstr | Optional | Specifies the category name for documents that have null values in the column split field. This parameter only applies when usenull is true. | "NULL" |
otherstr | Optional | Specifies the category name for the OTHER category. This parameter only applies when useother is true and there are values beyond the limit. | OTHER |
Notes
The following considerations apply when using the chart command:
- Fields generated by column splitting are converted to strings. This ensures compatibility with
nullstrandotherstrand allows the fields to be used as column names after pivoting. - Documents with null values in fields used by the aggregation function are excluded from aggregation. For example, in
chart avg(balance) over deptno, group, documents wherebalanceis null are excluded from the average calculation. - The aggregation metric appears as the last column in the results. Result columns are ordered as follows:
[row split] [column split] [aggregation metrics].
Example 1: Basic aggregation without grouping
This example calculates the average balance across all accounts:
source=accounts
| chart avg(balance)
The query returns the following results:
| avg(balance) |
|---|
| 20482.25 |
Example 2: Group by a single field
This example calculates the count of accounts grouped by gender:
source=accounts
| chart count() by gender
The query returns the following results:
| gender | count() |
|---|---|
| F | 1 |
| M | 3 |
Example 3: Using over [] by [] to group by multiple fields
The following query calculates average balance grouped by both the gender and age fields:
source=accounts
| chart avg(balance) over gender by age
The query returns the following results. The age column in the result is converted to the string type:
| gender | age | avg(balance) |
|---|---|---|
| F | 28 | 32838.0 |
| M | 32 | 39225.0 |
| M | 33 | 4180.0 |
| M | 36 | 5686.0 |
Example 4: Using basic limit functionality
This example limits the results to show only the single top age group:
source=accounts
| chart limit=1 count() over gender by age
The query returns the following results. The age column in the result is converted to the string type:
| gender | age | count() |
|---|---|---|
| F | OTHER | 1 |
| M | 33 | 1 |
| M | OTHER | 2 |
Example 5: Using limit with other parameters
The following query uses the chart command with the limit, useother, and custom otherstr parameters:
source=accounts
| chart limit=top1 useother=true otherstr='minor_gender' count() over state by gender
The query returns the following results:
| state | gender | count() |
|---|---|---|
| IL | M | 1 |
| MD | M | 1 |
| TN | M | 1 |
| VA | minor_gender | 1 |
Example 6: Using null parameters
The following query uses the chart command with the limit, usenull, and custom nullstr parameters:
source=accounts
| chart usenull=true nullstr='employer not specified' count() over firstname by employer
The query returns the following results:
| firstname | employer | count() |
|---|---|---|
| Amber | Pyrami | 1 |
| Dale | employer not specified | 1 |
| Hattie | Netagy | 1 |
| Nanette | Quility | 1 |
Example 7: Using span
The following query uses the chart command with span for grouping age ranges:
source=accounts
| chart max(balance) by age span=10, gender
The query returns the following results:
| age | gender | max(balance) |
|---|---|---|
| 20 | F | 32838 |
| 30 | M | 39225 |
Limitations
The chart command has the following limitations:
- Only a single aggregation function is supported per
chartcommand.