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
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, otherwiser 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, otherwiser 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 | +----------+-------------+------------+