You're viewing version 3.4 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation. For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy.
appendcol
The appendcol command appends the result of a subsearch as additional columns to the input search results (the main search).
Syntax
The appendcol command has the following syntax:
appendcol [override=<boolean>] <subsearch>
Parameters
The appendcol command supports the following parameters.
| Parameter | Required/Optional | Description |
|---|---|---|
<subsearch> | Required | Executes PPL commands as a secondary search. The subsearch uses the data specified in the source clause of the main search results as its input. |
override | Optional | Specifies whether the results of the main search should be overwritten when column names conflict. Default is false. |
Example 1: Append a count aggregation to existing search results
This example appends count by gender to sum by gender, state:
source=accounts
| stats sum(age) by gender, state
| appendcol [ stats count(age) by gender ]
| head 10
The query returns the following results:
| gender | state | sum(age) | count(age) |
|---|---|---|---|
| F | AK | 317 | 493 |
| F | AL | 397 | 507 |
| F | AR | 229 | NULL |
| F | AZ | 238 | NULL |
| F | CA | 282 | NULL |
| F | CO | 217 | NULL |
| F | CT | 147 | NULL |
| F | DC | 358 | NULL |
| F | DE | 101 | NULL |
| F | FL | 310 | NULL |
Example 2: Append a count aggregation to existing search results, overriding the main search results
This example appends count by gender to sum by gender, state and overrides the main search results:
source=accounts
| stats sum(age) by gender, state
| appendcol override=true [ stats count(age) by gender ]
| head 10
The query returns the following results:
| gender | state | sum(age) | count(age) |
|---|---|---|---|
| F | AK | 317 | 493 |
| M | AL | 397 | 507 |
| F | AR | 229 | NULL |
| F | AZ | 238 | NULL |
| F | CA | 282 | NULL |
| F | CO | 217 | NULL |
| F | CT | 147 | NULL |
| F | DC | 358 | NULL |
| F | DE | 101 | NULL |
| F | FL | 310 | NULL |
Example 3: Append multiple subsearch results
The following query chains multiple appendcol commands to add columns from different subsearches:
source=employees
| fields name, dept, age
| appendcol [ stats avg(age) as avg_age ]
| appendcol [ stats max(age) as max_age ]
The query returns the following results:
| name | dept | age | avg_age | max_age |
|---|---|---|---|---|
| Lisa | Sales | 35 | 31.2222222222222 | 38 |
| Fred | Engineering | 28 | NULL | NULL |
| Paul | Engineering | 23 | NULL | NULL |
| Evan | Sales | 38 | NULL | NULL |
| Chloe | Engineering | 25 | NULL | NULL |
| Tom | Engineering | 33 | NULL | NULL |
| Alex | Sales | 33 | NULL | NULL |
| Jane | Marketing | 28 | NULL | NULL |
| Jeff | Marketing | 38 | NULL | NULL |
Example 4: Resolve column name conflicts using the override parameter
The following query shows how to use appendcol with the override option when column names in the main search and subsearch conflict:
source=employees
| stats avg(age) as agg by dept
| appendcol override=true [ stats max(age) as agg by dept ]
The query returns the following results:
| agg | dept |
|---|---|
| 38 | Sales |
| 38 | Engineering |
| 38 | Marketing |