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.

where

The where command filters the search results. It only returns results that match the specified conditions.

Syntax

The where command has the following syntax:

where <boolean-expression>

Parameters

The where command supports the following parameters.

Example 1: Filter by numeric values

The following query returns accounts in which balance is greater than 30000:

source=accounts
| where balance > 30000
| fields account_number, balance

The query returns the following results:

Example 2: Filter using combined criteria

The following query combines multiple conditions using an AND operator:

source=accounts
| where age > 30 AND gender = 'M'
| fields account_number, age, gender

The query returns the following results:

Example 3: Filter with multiple possible values

The following query fetches all the documents from the accounts index in which account_number is 1 or gender is F:

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

The query returns the following results:

Example 4: Filter by text patterns

The LIKE operator enables pattern matching on string fields using wildcards.

Matching a single character

The following query uses an underscore (_) to match a single character:

source=accounts
| where LIKE(state, 'M_')
| fields account_number, state

The query returns the following results:

Matching multiple characters

The following query uses a percent sign (%) to match multiple characters:

source=accounts
| where LIKE(state, 'V%')
| fields account_number, state

The query returns the following results:

Example 5: Filter by excluding specific values

The following query uses a NOT operator to exclude matching records:

source=accounts
| where NOT state = 'CA'
| fields account_number, state

The query returns the following results:

Example 6: Filter using value lists

The following query uses an IN operator to match multiple values:

source=accounts
| where state IN ('IL', 'VA')
| fields account_number, state

The query returns the following results:

Example 7: Filter records with missing data

The following query returns records in which the employer field is null:

source=accounts
| where ISNULL(employer)
| fields account_number, employer

The query returns the following results:

Example 8: Filter using grouped conditions

The following query combines multiple conditions using parentheses and logical operators:

source=accounts
| where (balance > 40000 OR age > 35) AND gender = 'M'
| fields account_number, balance, age, gender

The query returns the following results: