You're viewing version 1.2 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.

Commands

Start a PPL query with a search command to reference a table to search from. You can have the commands that follow in any order.

In the following example, the search command refers to an accounts index as the source, then uses fields and where commands for the conditions:

search source=accounts
| where age > 18
| fields firstname, lastname

In the below examples, we represent required arguments in angle brackets < > and optional arguments in square brackets [ ].

search

Use the search command to retrieve a document from an index. You can only use the search command as the first command in the PPL query.

Syntax

search source=<index> [boolean-expression]

Example 1: Get all documents

To get all documents from the accounts index:

search source=accounts;

Example 2: Get documents that match a condition

To get all documents from the accounts index that have either account_number equal to 1 or have gender as F:

search source=accounts account_number=1 or gender=\"F\";

dedup

The dedup (data deduplication) command removes duplicate documents defined by a field from the search result.

Syntax

dedup [int] <field-list> [keepempty=<bool>] [consecutive=<bool>]

Example 1: Dedup by one field

To remove duplicate documents with the same gender:

search source=accounts | dedup gender | fields account_number, gender;

Example 2: Keep two duplicate documents

To keep two duplicate documents with the same gender:

search source=accounts | dedup 2 gender | fields account_number, gender;

Example 3: Keep or ignore an empty field by default

To keep two duplicate documents with a null field value:

search source=accounts | dedup email keepempty=true | fields account_number, email;

To remove duplicate documents with the null field value:

search source=accounts | dedup email | fields account_number, email;

Example 4: Dedup of consecutive documents

To remove duplicates of consecutive documents:

search source=accounts | dedup gender consecutive=true | fields account_number, gender;

eval

The eval command evaluates an expression and appends its result to the search result.

Syntax

eval <field>=<expression> ["," <field>=<expression> ]...

Example 1: Create a new field

To create a new doubleAge field for each document. doubleAge is the result of age multiplied by 2:

search source=accounts | eval doubleAge = age * 2 | fields age, doubleAge;

Example 2: Overwrite the existing field

To overwrite the age field with age plus 1:

search source=accounts | eval age = age + 1 | fields age;

Example 3: Create a new field with a field defined with the eval command

To create a new field ddAge. ddAge is the result of doubleAge multiplied by 2, where doubleAge is defined in the eval command:

search source=accounts | eval doubleAge = age * 2, ddAge = doubleAge * 2 | fields age, doubleAge, ddAge;

fields

Use the fields command to keep or remove fields from a search result.

Syntax

fields [+|-] <field-list>

Example 1: Select specified fields from result

To get account_number, firstname, and lastname fields from a search result:

search source=accounts | fields account_number, firstname, lastname;

Example 2: Remove specified fields from a search result

To remove the account_number field from the search results:

search source=accounts | fields account_number, firstname, lastname | fields - account_number;

rename

Use the rename command to rename one or more fields in the search result.

Syntax

rename <source-field> AS <target-field>["," <source-field> AS <target-field>]...

Example 1: Rename one field

Rename the account_number field as an:

search source=accounts | rename account_number as an | fields an;

Example 2: Rename multiple fields

Rename the account_number field as an and employer as emp:

search source=accounts | rename account_number as an, employer as emp | fields an, emp;

sort

Use the sort command to sort search results by a specified field.

Syntax

sort [count] <[+|-] sort-field>...

Example 1: Sort by one field

To sort all documents by the age field in ascending order:

search source=accounts | sort age | fields account_number, age;

Example 2: Sort by one field and return all results

To sort all documents by the age field in ascending order and specify count as 0 to get back all results:

search source=accounts | sort 0 age | fields account_number, age;

Example 3: Sort by one field in descending order

To sort all documents by the age field in descending order:

search source=accounts | sort - age | fields account_number, age;

Example 4: Specify the number of sorted documents to return

To sort all documents by the age field in ascending order and specify count as 2 to get back two results:

search source=accounts | sort 2 age | fields account_number, age;

Example 5: Sort by multiple fields

To sort all documents by the gender field in ascending order and age field in descending order:

search source=accounts | sort + gender, - age | fields account_number, gender, age;

stats

Use the stats command to aggregate from search results.

The following table lists the aggregation functions and also indicates how each one handles null or missing values:

Syntax

stats <aggregation>... [by-clause]...

Example 1: Calculate the average value of a field

To calculate the average age of all documents:

search source=accounts | stats avg(age);

Example 2: Calculate the average value of a field by group

To calculate the average age grouped by gender:

search source=accounts | stats avg(age) by gender;

Example 3: Calculate the average and sum of a field by group

To calculate the average and sum of age grouped by gender:

search source=accounts | stats avg(age), sum(age) by gender;

Example 4: Calculate the maximum value of a field

To calculate the maximum age:

search source=accounts | stats max(age);

Example 5: Calculate the maximum and minimum value of a field by group

To calculate the maximum and minimum age values grouped by gender:

search source=accounts | stats max(age), min(age) by gender;

where

Use the where command with a bool expression to filter the search result. The where command only returns the result when the bool expression evaluates to true.

Syntax

where <boolean-expression>

Example 1: Filter result set with a condition

To get all documents from the accounts index where account_number is 1 or gender is F:

search source=accounts | where account_number=1 or gender=\"F\" | fields account_number, gender;

head

Use the head command to return the first N number of results in a specified search order.

Syntax

head [N]

Example 1: Get the first 10 results

To get the first 10 results:

search source=accounts | fields firstname, age | head;

Example 2: Get the first N results

To get the first two results:

search source=accounts | fields firstname, age | head 2;

rare

Use the rare command to find the least common values of all fields in a field list. A maximum of 10 results are returned for each distinct set of values of the group-by fields.

Syntax

rare <field-list> [by-clause]

Example 1: Find the least common values in a field

To find the least common values of gender:

search source=accounts | rare gender;

Example 2: Find the least common values grouped by gender

To find the least common age grouped by gender:

search source=accounts | rare age by gender;

top

Use the top command to find the most common values of all fields in the field list.

Syntax

top [N] <field-list> [by-clause]

Example 1: Find the most common values in a field

To find the most common genders:

search source=accounts | top gender;

Example 2: Find the most common value in a field

To find the most common gender:

search source=accounts | top 1 gender;

Example 2: Find the most common values grouped by gender

To find the most common age grouped by gender:

search source=accounts | top 1 age by gender;

match

Use the match command to search documents that match a string, number, date, or boolean value for a given field.

Syntax

match(field_expression, query_expression[, option=<option_value>]*)

You can specify the following options:

• analyzer
• auto_generate_synonyms_phrase
• fuzziness
• max_expansions
• prefix_length
• fuzzy_transpositions
• fuzzy_rewrite
• lenient
• operator
• minimum_should_match
• zero_terms_query
• boost

Example 1: Search the message field:

GET my_index/_search
{
  "query": {
    "match": {
      "message": "this is a test"
    }
  }
}

PPL query:

search source=my_index | match field=message query="this is a test"

Example 2: Search the message field with the operator parameter:

GET my_index/_search
{
  "query": {
    "match": {
      "message": {
        "query": "this is a test",
        "operator": "and"
      }
    }
  }
}

PPL query:

search source=my_index | match field=message query="this is a test" operator=and

Example 3: Search the message field with the operator and zero_terms_query parameters:

GET my_index/_search
{
  "query": {
    "match": {
      "message": {
        "query": "to be or not to be",
        "operator": "and",
        "zero_terms_query": "all"
      }
    }
  }
}

PPL query:

search source=my_index | where match(message, "this is a test", operator=and, zero_terms_query=all)