coalesce

This page explains how to use the coalesce function in APL.

The coalesce function evaluates a list of expressions and returns the first non-null (or non-empty for strings) value. Use this function to handle missing data, provide default values, or select the first available field from multiple options in your queries.

For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.

Splunk SPL users

In Splunk SPL, you use the coalesce command similarly to APL, but the syntax is slightly different. APL’s coalesce works identically to Splunk’s version.

Splunk example

| eval result=coalesce(field1, field2, field3)

ANSI SQL users

In ANSI SQL, COALESCE is a standard function with the same behavior. APL’s coalesce function works identically to SQL’s COALESCE.

SQL example

SELECT COALESCE(field1, field2, field3) AS result FROM logs;

Usage

Syntax

coalesce(expr1, expr2, ..., exprN)

Parameters

Name	Type	Required	Description
expr1, expr2, …, exprN	scalar	Yes	A list of expressions to evaluate. At least one expression is required.

Returns

Returns the value of the first expression that is not null. For string expressions, returns the first non-empty string.

Use case examples

Log analysis

Provide fallback values when analyzing HTTP logs where certain fields might be missing or empty.

Query

['sample-http-logs']
| extend location = coalesce(['geo.city'], ['geo.country'], 'Unknown')
| summarize request_count = count() by location
| sort by request_count desc
| limit 10

Run in Playground

Output

location	request_count
New York	1523
London	987
United States	654
Unknown	234

This query uses coalesce to select the city if available, fall back to country if city is missing, and finally use ‘Unknown’ if both are missing, ensuring comprehensive location tracking.

List of related functions

• isnotnull: Checks if a value is not null. Use this to explicitly test for null values before using coalesce.
• isnull: Checks if a value is null. Use this to identify which values would be skipped by coalesce.
• isempty: Checks if a string is empty or null. Use this with coalesce when working specifically with string data.
• isnotempty: Checks if a string is not empty and not null. Use this to validate strings before coalescing them.