Commands

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

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

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.

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:

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;

To remove the account_number field from the search results:

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

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);

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;

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

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
• 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

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)