Working with NULL values in ES|QL

NULL represents a value that is unknown, missing, or unavailable in a result row. It is common when a document has no value for a field, when a field is not mapped for part of a query, or when an expression cannot produce a value.

The most important thing to know is that NULL is not the same as false, an empty string, or 0. Many expressions that involve NULL evaluate to NULL, and WHERE keeps only rows where the condition is true. This can make rows disappear unless you handle NULL explicitly.

Use this page to avoid the most common NULL gotchas, then refer to the sections that follow for the details behind each rule.

Use these patterns to avoid unexpected behavior:

Use IS NULL and IS NOT NULL to test whether an expression evaluates to a NULL value.

The following query returns rows where languages evaluates to NULL:

FROM employees
| WHERE languages IS NULL
		

Do not use equality or inequality comparisons to test for NULL. A comparison with NULL evaluates to NULL, not to true or false.

The following query compares equality and inequality checks with the IS NULL predicate:

ROW x = NULL
| EVAL eq_null = x == NULL, neq_null = x != NULL, is_null = x IS NULL
		

       x | eq_null | neq_null | is_null
---------+---------+----------+--------
    null |    null |     null |    true
		

Refer to IS NULL and IS NOT NULL.

Comparisons involving NULL evaluate to NULL. This includes ==, !=, <, <=, >, and >=.

This can be surprising with exclusions. The following query does not keep rows where process.name is NULL:

FROM logs
| WHERE process.name != "svchost.exe"
		

When process.name is NULL, the comparison evaluates to NULL, and WHERE drops the row. If you want to keep rows with a different process name and rows with no process name, include the null case explicitly:

FROM logs
| WHERE process.name != "svchost.exe" OR process.name IS NULL
		

The same rule applies when negating a comparison:

FROM logs
| WHERE NOT process.name == "svchost.exe"
		

Rows where process.name is NULL are still dropped, because process.name == "svchost.exe" evaluates to NULL, and NOT NULL is also NULL.

Boolean operators use three-valued logic. NULL means unknown, so it is preserved unless the other operand determines the result.

Read the AND and OR tables as operator result matrices. Choose the left operand from the first column and the right operand from the header row; the cell where they meet is the result.

WHERE keeps rows only when the condition evaluates to true. Rows where the condition evaluates to false or NULL are filtered out.

The following query returns no rows, because x == NULL evaluates to NULL:

ROW x = NULL
| WHERE x == NULL
		

Empty result set
		

Use IS NULL to keep rows where an expression evaluates to NULL:

ROW x = NULL
| WHERE x IS NULL
		

       x
--------
    null
		

The IS NULL predicate returns true, so WHERE keeps the row.

If you want a filter to keep rows where a value is either missing or matches another condition, include both cases:

FROM employees
| WHERE languages < 3 OR languages IS NULL
		

Many scalar functions return NULL when an input is NULL. This preserves unknown or unavailable values instead of inventing a result.

Use conditional functions and expressions when you want to replace or branch on NULL values:

ROW department = NULL
| EVAL department = COALESCE(department, "Unknown")
		

department
----------
Unknown
		

Useful references:

• COALESCE returns the first non-null value.
• CASE chooses a result based on conditions.
• Type conversion functions can produce NULL when a value cannot be converted.

For exact behavior, check the reference page for the function you are using.

Aggregate functions handle NULL depending on the function.

Common cases:

• COUNT(*) and COUNT() count rows.
• COUNT(field) counts non-null values in field.
• COUNT(NULL) returns 0.
• COUNT(false) returns 1, because false is a non-null value.
• Other aggregates generally ignore null input values and return NULL when there are no values to aggregate. Check each aggregate function's reference page for details.
• Grouping by a NULL expression creates a group with a NULL key.

The following query shows the difference between counting rows and counting non-null values:

ROW x = NULL
| STATS rows = COUNT(*), values = COUNT(x), nulls = COUNT(NULL)
		

    rows |  values |   nulls
---------+---------+--------
       1 |       0 |       0
		

Do not use COUNT(condition OR NULL) unless you specifically want to rely on three-valued logic. Prefer a filtered aggregate:

FROM employees
| STATS hired = COUNT(*) WHERE still_hired
		

Other aggregates commonly return NULL when every input value is NULL. The following query has a row to aggregate, but no non-null value for SUM:

ROW x = NULL
| STATS sum_x = SUM(TO_INTEGER(x))
		

   sum_x
--------
    null
		

Refer to COUNT and aggregation functions.

By default, NULL values are treated as larger than any other value. This means:

• ascending sorts put NULL values last
• descending sorts put NULL values first

Use NULLS FIRST or NULLS LAST to choose a different placement:

FROM employees
| SORT first_name ASC NULLS FIRST
		

Refer to SORT.

Missing values, unmapped fields, and NULL are related but not the same thing.

• A missing value is a document or result row that has no value for a field. In ES|QL, this often appears as NULL.
• An unmapped field is a field that exists in indexed documents but is not defined in the index mapping. This is a schema-level condition.
• With SET unmapped_fields = "nullify" , fully unmapped fields return NULL.
• With SET unmapped_fields = "load" , ES|QL can load real values from _source; values absent from _source still return NULL.
• Runtime fields are computed fields in the mapping. ES|QL treats mapped runtime fields like other mapped fields.

Refer to unmapped fields and SET unmapped_fields.

A multivalued field with one or more values is different from NULL. An empty multivalued field can appear as NULL in ES|QL.

Some scalar comparisons and functions return NULL when a multivalued value cannot be reduced to a single value. Also, MV_APPEND currently returns NULL when either input is NULL.

The following query appends a NULL value to a multivalued value:

ROW values = MV_APPEND(1, 2)
| EVAL append_null = MV_APPEND(values, NULL)
		

  values | append_null
---------+------------
  [1, 2] |        null
		

Refer to multivalued fields and MV_APPEND.

• WHERE
• SORT
• IS NULL and IS NOT NULL
• Conditional functions and expressions
• Aggregation functions
• Multivalued fields
• Unmapped fields