Axiom
Overview

coalesce

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's not null. For string expressions, returns the first non-empty string.

Use case examples#

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.

Handle missing or null span attributes in distributed traces by providing default values.

Query

['otel-demo-traces']
| extend span_kind = coalesce(kind, 'unknown')
| summarize span_count = count() by span_kind, ['service.name']
| sort by span_count desc
| limit 10

Run in Playground

Output

span_kind service.name span_count
server frontend 2345
client checkout 1876
internal cart 1234
unknown product-catalog 567

This query uses coalesce to provide a default value for span kinds, ensuring that traces with missing kind information are still included in the analysis.

Ensure user identification in security logs by selecting from multiple possible identifier fields.

Query

['sample-http-logs']
| extend user_identifier = coalesce(id, uri, 'anonymous')
| summarize failed_attempts = count() by user_identifier, status
| sort by failed_attempts desc
| limit 10

Run in Playground

Output

user_identifier status failed_attempts
user123 401 45
user456 403 32
/admin 401 28
anonymous 401 15

This query uses coalesce to identify users from failed authentication attempts, trying the user ID first, then falling back to the URI, and finally marking truly anonymous attempts.

  • isnotnull: Checks if a value isn't 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 isn't empty and not null. Use this to validate strings before coalescing them.

Other query languages#

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)

APL equivalent

['sample-http-logs']
| extend 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;

APL equivalent

['sample-http-logs']
| extend result = coalesce(field1, field2, field3)

Updated

Was this page helpful?