Set Default Value For Null Fields in Queries

Handle missing values in queries using the isNull() function with if()

Query

flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{Conditional} result{{Result Set}} repo --> 1 1 --> result
logscale
bar := if(isNull(foo), then=0, else=foo/100)

Introduction

The if() function can be used with isNull() to provide default values for fields that are missing or contain null values.

In this example, the if() function is used with isNull() to check for null values and provide a default value before performing a calculation.

Example incoming data might look like this:

@timestampfoo
2025-10-15T10:00:00<no value>
2025-10-15T10:00:0150
2025-10-15T10:00:0275
2025-10-15T10:00:03<no value>
2025-10-15T10:00:04100
2025-10-15T10:00:0525

Step-by-Step

  1. Starting with the source repository events.

  2. flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 1{Conditional} result{{Result Set}} repo --> 1 1 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
    logscale
    bar := if(isNull(foo), then=0, else=foo/100)

    Creates a new field named bar using the if() function with isNull() to handle missing values:

    • The isNull() function checks if foo is null or empty, returning true if it is.

    • If isNull(foo) is true, assigns the default value of 0.

    • If isNull(foo) is false, divides the value of foo by 100.

    This pattern ensures that calculations can proceed safely even when input data is missing, by providing a meaningful default value.

  3. Event Result set.

Summary and Results

The query is used to handle missing values in data by providing a default value, ensuring that subsequent calculations can proceed without errors.

This query is useful, for example, to normalize percentage values while handling missing data in a consistent way, or to prevent calculation errors when processing data with potential gaps.

Sample output from the incoming example data:

barfoo
0<no value>
0.550
0.7575
0<no value>
1100
0.2525

Note that in the output, when foo is null or empty, the bar field also remains empty. For non-null values in foo, the values are divided by 100 to convert them to decimal form (for example, 50 becomes 0.5).