Table of contents
Usage: isnull(field) return true if field is null.
Argument type: all the supported data type.
Return type: BOOLEAN
Example:
os> source=accounts | eval result = isnull(employer) | fields result, employer, firstname fetched rows / total rows = 4/4 +--------+----------+-----------+ | result | employer | firstname | |--------+----------+-----------| | False | Pyrami | Amber | | False | Netagy | Hattie | | False | Quility | Nanette | | True | null | Dale | +--------+----------+-----------+
Usage: isnotnull(field) return true if field is not null.
Argument type: all the supported data type.
Return type: BOOLEAN
Synonyms: ISPRESENT
Example:
os> source=accounts | where not isnotnull(employer) | fields account_number, employer fetched rows / total rows = 1/1 +----------------+----------+ | account_number | employer | |----------------+----------| | 18 | null | +----------------+----------+
Because OpenSearch doesn't differentiate null and missing. so we can't provide function like ismissing/isnotmissing to test field exist or not. But you can still use isnull/isnotnull for such purpose.
Example, the account 13 doesn't have email field:
os> source=accounts | where isnull(email) | fields account_number, email fetched rows / total rows = 1/1 +----------------+-------+ | account_number | email | |----------------+-------| | 13 | null | +----------------+-------+
Usage: ifnull(field1, field2) return field2 if field1 is null.
Argument type: all the supported data type, (NOTE : if two parameters has different type, you will fail semantic check.)
Return type: any
Example:
os> source=accounts | eval result = ifnull(employer, 'default') | fields result, employer, firstname fetched rows / total rows = 4/4 +---------+----------+-----------+ | result | employer | firstname | |---------+----------+-----------| | Pyrami | Pyrami | Amber | | Netagy | Netagy | Hattie | | Quility | Quility | Nanette | | default | null | Dale | +---------+----------+-----------+
Usage: nullif(field1, field2) return null if two parameters are same, otherwise return field1.
Argument type: all the supported data type, (NOTE : if two parameters has different type, if two parameters has different type, you will fail semantic check)
Return type: any
Example:
os> source=accounts | eval result = nullif(employer, 'Pyrami') | fields result, employer, firstname fetched rows / total rows = 4/4 +---------+----------+-----------+ | result | employer | firstname | |---------+----------+-----------| | null | Pyrami | Amber | | Netagy | Netagy | Hattie | | Quility | Quility | Nanette | | null | null | Dale | +---------+----------+-----------+
Usage: isnull(field1, field2) return null if two parameters are same, otherwise return field1.
Argument type: all the supported data type
Return type: any
Example:
os> source=accounts | eval result = isnull(employer) | fields result, employer, firstname fetched rows / total rows = 4/4 +--------+----------+-----------+ | result | employer | firstname | |--------+----------+-----------| | False | Pyrami | Amber | | False | Netagy | Hattie | | False | Quility | Nanette | | True | null | Dale | +--------+----------+-----------+
Usage: if(condition, expr1, expr2) return expr1 if condition is true, otherwise return expr2.
Argument type: all the supported data type, (NOTE : if expr1 and expr2 are different type, you will fail semantic check
Return type: any
Example:
os> source=accounts | eval result = if(true, firstname, lastname) | fields result, firstname, lastname fetched rows / total rows = 4/4 +---------+-----------+----------+ | result | firstname | lastname | |---------+-----------+----------| | Amber | Amber | Duke | | Hattie | Hattie | Bond | | Nanette | Nanette | Bates | | Dale | Dale | Adams | +---------+-----------+----------+ os> source=accounts | eval result = if(false, firstname, lastname) | fields result, firstname, lastname fetched rows / total rows = 4/4 +--------+-----------+----------+ | result | firstname | lastname | |--------+-----------+----------| | Duke | Amber | Duke | | Bond | Hattie | Bond | | Bates | Nanette | Bates | | Adams | Dale | Adams | +--------+-----------+----------+ os> source=accounts | eval is_vip = if(age > 30 AND isnotnull(employer), true, false) | fields is_vip, firstname, lastname fetched rows / total rows = 4/4 +--------+-----------+----------+ | is_vip | firstname | lastname | |--------+-----------+----------| | True | Amber | Duke | | True | Hattie | Bond | | False | Nanette | Bates | | False | Dale | Adams | +--------+-----------+----------+
Usage: case(condition1, expr1, condition2, expr2, ... conditionN, exprN else default) return expr1 if condition1 is true, or return expr2 if condition2 is true, ... if no condition is true, then return the value of ELSE clause. If the ELSE clause is not defined, it returns NULL.
Argument type: all the supported data type, (NOTE : there is no comma before "else")
Return type: any
Example:
os> source=accounts | eval result = case(age > 35, firstname, age < 30, lastname else employer) | fields result, firstname, lastname, age, employer fetched rows / total rows = 4/4 +--------+-----------+----------+-----+----------+ | result | firstname | lastname | age | employer | |--------+-----------+----------+-----+----------| | Pyrami | Amber | Duke | 32 | Pyrami | | Hattie | Hattie | Bond | 36 | Netagy | | Bates | Nanette | Bates | 28 | Quility | | null | Dale | Adams | 33 | null | +--------+-----------+----------+-----+----------+ os> source=accounts | eval result = case(age > 35, firstname, age < 30, lastname) | fields result, firstname, lastname, age fetched rows / total rows = 4/4 +--------+-----------+----------+-----+ | result | firstname | lastname | age | |--------+-----------+----------+-----| | null | Amber | Duke | 32 | | Hattie | Hattie | Bond | 36 | | Bates | Nanette | Bates | 28 | | null | Dale | Adams | 33 | +--------+-----------+----------+-----+ os> source=accounts | where true = case(age > 35, false, age < 30, false else true) | fields firstname, lastname, age fetched rows / total rows = 2/2 +-----------+----------+-----+ | firstname | lastname | age | |-----------+----------+-----| | Amber | Duke | 32 | | Dale | Adams | 33 | +-----------+----------+-----+
Version: 3.1.0
Usage: coalesce(field1, field2, ...) return the first non-null value in the argument list.
Argument type: all the supported data type. The data types of all fields must be same.
Return type: any
Example:
PPL> source=accounts | eval result = coalesce(employer, firstname, lastname) | fields result, firstname, lastname, employer fetched rows / total rows = 4/4 +---------+-----------+----------+----------+ | result | firstname | lastname | employer | |---------+-----------+----------+----------| | Pyrami | Amber | Duke | Pyrami | | Netagy | Hattie | Bond | Netagy | | Quility | Nanette | Bates | Quility | | Dale | Dale | Adams | null | +---------+-----------+----------+----------+
Version: 3.1.0
Usage: ispresent(field) return true if the field exists.
Argument type: all the supported data type.
Return type: BOOLEAN
Synonyms: ISNOTNULL
Example:
PPL> source=accounts | where ispresent(employer) | fields employer, firstname fetched rows / total rows = 3/3 +----------+-----------+ | employer | firstname | |----------+-----------| | Pyrami | Amber | | Netagy | Hattie | | Quility | Nanette | +----------+-----------+
Version: 3.1.0
Usage: isblank(field) returns true if the field is null, an empty string, or contains only white space.
Argument type: all the supported data type.
Return type: BOOLEAN
Example:
PPL> source=accounts | eval temp = ifnull(employer, ' ') | eval `isblank(employer)` = isblank(employer), `isblank(temp)` = isblank(temp) | fields `isblank(temp)`, temp, `isblank(employer)`, employer fetched rows / total rows = 4/4 +---------------+---------+-------------------+----------+ | isblank(temp) | temp | isblank(employer) | employer | |---------------+---------+-------------------+----------| | False | Pyrami | False | Pyrami | | False | Netagy | False | Netagy | | False | Quility | False | Quility | | True | | True | null | +---------------+---------+-------------------+----------+
Version: 3.1.0
Usage: isempty(field) returns true if the field is null or is an empty string.
Argument type: all the supported data type.
Return type: BOOLEAN
Example:
PPL> source=accounts | eval temp = ifnull(employer, ' ') | eval `isempty(employer)` = isempty(employer), `isempty(temp)` = isempty(temp) | fields `isempty(temp)`, temp, `isempty(employer)`, employer fetched rows / total rows = 4/4 +---------------+---------+-------------------+----------+ | isempty(temp) | temp | isempty(employer) | employer | |---------------+---------+-------------------+----------| | False | Pyrami | False | Pyrami | | False | Netagy | False | Netagy | | False | Quility | False | Quility | | False | | True | null | +---------------+---------+-------------------+----------+
Version: 3.1.0
Usage: earliest(relative_string, field) returns true if the value of field is after the timestamp derived from relative_string relative to the current time. Otherwise, return false.
relative_string: The relative string can be one of the following formats:
"now" or "now()": Uses the current system time.
Absolute format (MM/dd/yyyy:HH:mm:ss): Converts the string to a timestamp and compares it with the data.
Relative format: (+|-)<time_integer><time_unit>[+<...>]@<snap_unit> Steps to specify a relative time:
- a. Time offset: Indicate the offset from the current time using + or -.
- b. Time amount: Provide a numeric value followed by a time unit (s, m, h, d, w, M, y).
- c. Snap to unit: Optionally specify a snap unit with @<unit> to round the result down to the nearest unit (e.g., hour, day, month).
Examples (assuming current time is 2025-05-28 14:28:34):
- -3d+2y → 2027-05-25 14:28:34
- +1d@m → 2025-05-29 14:28:00
- -3M+1y@M → 2026-02-01 00:00:00
Read more details here
Argument type: relative_string:STRING, field: TIMESTAMP
Return type: BOOLEAN
Example:
PPL> source=accounts | eval now = utc_timestamp() | eval a = earliest("now", now), b = earliest("-2d@d", now) | fields a, b | head 1
fetched rows / total rows = 1/1
+-------+-------+
| a | b |
|-------+-------|
| False | True |
+-------+-------+
PPL> source=nyc_taxi | where earliest('07/01/2014:00:30:00', timestamp) | stats COUNT() as cnt
fetched rows / total rows = 1/1
+-----+
| cnt |
|-----|
| 971 |
+-----+
Version: 3.1.0
Usage: latest(relative_string, field) returns true if the value of field is before the timestamp derived from relative_string relative to the current time. Otherwise, return false.
Argument type: relative_string:STRING, field: TIMESTAMP
Return type: BOOLEAN
Example:
PPL> source=accounts | eval now = utc_timestamp() | eval a = latest("now", now), b = latest("+2d@d", now) | fields a, b | head 1
fetched rows / total rows = 1/1
+-------+-------+
| a | b |
|-------+-------|
| False | True |
+-------+-------+
PPL> source=nyc_taxi | where latest('07/21/2014:04:00:00', timestamp) | stats COUNT() as cnt
fetched rows / total rows = 1/1
+-----+
| cnt |
|-----|
| 968 |
+-----+