replacenull ↗

Use the replacenull stage to ensure data completeness by replacing null (or null-equivalent) values within a specified field with a default value. The stage is schema-aware: it uses the field's metadata type to determine which replacement value is valid, and supports both primitive types (String, Number, Boolean, Enum) and complex types (Array, JSON, Datetime).

Syntax

replacenull <field> = <replacement_value>

Parameters

Name Type Required Description
field string Yes The name of the field whose null (or null-equivalent) values you want to replace.
replacement_value string, number, boolean, array, JSON, or datetime Yes The value that replaces any NULL (or null-equivalent) occurrences in the specified field. The value must match the data type of the field. Literal strings must be enclosed in double quotes, and complex types must be constructed with the appropriate function (see Usage notes).

Returns

The replacenull stage returns the dataset with the specified field modified, where all NULL (and null-equivalent) values have been substituted with the provided replacement value. All other records and fields are returned unchanged.

Usage notes

  • The replacenull stage is schema-aware. It reads the field's metadata type before applying the replacement logic, so the replacement_value you provide must be compatible with the field's data type.

  • The stage supports the following field types, each with its own replacement rules:

    Field type Null-equivalent condition How to specify the replacement value
    String NULL A quoted string literal, for example "invalid host".
    Number NULL A numeric literal, for example 0.
    Boolean NULL true or false.
    Enum NULL A valid enum value.
    Array NULL or empty array [] An array constructed with arraycreate(), for example arraycreate("no-record").
    JSON NULL A JSON object, typically an empty object {} or one constructed with object_create().
    Datetime NULL A quoted datetime string in a supported format (see below).
  • Empty array detection: For Array fields, both NULL values and empty arrays ([]) are treated as null-equivalent and are replaced.
  • Datetime formats: For Datetime fields, the replacement value must be provided as a quoted string in one of the following supported formats:
    • ISO 8601 with Z: %Y-%m-%dT%H:%M:%SZ (for example, "1970-01-01T00:00:00Z")
    • ISO 8601 without Z: %Y-%m-%dT%H:%M:%S (for example, "1970-01-01T00:00:00")
    • Standard format: %Y-%m-%d %H:%M:%S (for example, "1970-01-01 00:00:00")
  • If you employ the replacenull stage, any subsequent stages that refer to the modified field's value must use the newly defined replacement value instead of checking for NULL.
  • Apply filter stages as early as possible in your query. This reduces the total dataset size before replacenull processes the records, minimizing the amount of data it needs to operate on.
  • Use the fields stage immediately after initial filtering to select only the necessary columns. This reduces the data footprint passed to replacenull and subsequent stages, improving overall query performance.

Examples

Example 1: Basic replacenull on a String field

Goal: Replace NULL values in a primitive String field with a default label.

XQL code:

dataset = xdr_data
| comp count() as count by agent_hostname
| replacenull agent_hostname = "invalid host"
| limit 100

Explanation: The replacenull agent_hostname = "invalid host" stage replaces any NULL values in the agent_hostname field with the string "invalid host".

Output:

COUNT AGENT_HOSTNAME
42 server-01
15 invalid host
7 server-03

Example 2: replacenull on an Array field (empty arrays and nulls)

Goal: Replace both NULL values and empty arrays ([]) in an Array field with a default array.

XQL code:

dataset = xdr_data
| fields host, dns_records
| replacenull dns_records = arraycreate("no-record")
| limit 100

Explanation: Because dns_records is an Array field, both NULL values and empty arrays are treated as null-equivalent. The replacenull dns_records = arraycreate("no-record") stage replaces those values with the array ["no-record"].

Before replacenull:

HOST DNS_RECORDS
server-01 ["a.example.com", "b.example.com"]
server-02 null
server-03 []

After replacenull:

HOST DNS_RECORDS
server-01 ["a.example.com", "b.example.com"]
server-02 ["no-record"]
server-03 ["no-record"]

Example 3: Type-specific replacements across multiple field types

Goal: Apply replacenull to several fields of different types — String, Number, Datetime, and JSON — in a single query, using a replacement value appropriate to each type.

XQL code:

dataset = xdr_data
| replacenull host = "N/A"
| replacenull severity = 0
| replacenull event_time = "1970-01-01T00:00:00Z"
| replacenull metadata = object_create("env", "dev")
| limit 100

Explanation: Each replacenull stage targets a field of a specific type:

  • host (String) — NULL values are replaced with "N/A".
  • severity (Number) — NULL values are replaced with 0.
  • event_time (Datetime) — NULL values are replaced with the ISO 8601 datetime "1970-01-01T00:00:00Z".
  • metadata (JSON) — NULL values are replaced with the object {"env": "dev"} constructed by object_create().

Before replacenull:

HOST SEVERITY EVENT_TIME METADATA
server-01 null 2026-03-24T10:30:00Z null
null 5 null {"env": "prod"}
null null null {}

After replacenull:

HOST SEVERITY EVENT_TIME METADATA
server-01 0 2026-03-24T10:30:00Z {"env": "dev"}
N/A 5 1970-01-01T00:00:00Z {"env": "prod"}
N/A 0 1970-01-01T00:00:00Z {}

Example 4: replacenull on a field derived from a JSON function

Goal: Replace NULL values in a field created by an alter stage using a JSON function, where the extracted value can be NULL if the key does not exist.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, simple_json_data // Select relevant fields, including the JSON source
| alter json_code = json_extract_scalar(simple_json_data, "$.code") // Extract 'code'; can be NULL
| replacenull json_code = "Code Not Found" // Replace NULLs in the new 'json_code' field
| limit 5

Explanation: The query extracts the value of the "code" key using json_extract_scalar(simple_json_data, "$.code"). For records where this key does not exist (102, 103, 104, 105), the result is NULL. The replacenull json_code = "Code Not Found" stage then replaces these NULL values.

Output:

EVENT_ID SIMPLE_JSON_DATA JSON_CODE
101 {"status": "ok", "code": 200} 200
102 {"status": "fail", "error": "access_denied"} Code Not Found
103 {"connection_id": "CONN-001", "protocol": "TCP"} Code Not Found
104 {"health": "good"} Code Not Found
105 {"transform_stage": 1} Code Not Found

Example 5: Filtering based on the replaced value

Goal: Demonstrate how subsequent filter stages must use the replacement value when querying for values that were originally NULL.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, ipv4_address // Select necessary fields
| replacenull ipv4_address = "UNKNOWN_IP" // Replace NULLs in ipv4_address
| filter ipv4_address = "UNKNOWN_IP" // Filter using the replacement value
| limit 5

Explanation: The replacenull ipv4_address = "UNKNOWN_IP" stage replaces NULL entries in ipv4_address. Since ipv4_address is NULL for event_id 103, 107, and 109, the subsequent filter filter ipv4_address = "UNKNOWN_IP" successfully identifies and returns only those records.

Output:

EVENT_ID IPV4_ADDRESS
103 UNKNOWN_IP
107 UNKNOWN_IP
109 UNKNOWN_IP
  • Stages: alter, filter, fields, comp
  • Functions: arraycreate, object_create, json_extract_scalar, coalesce