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.
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 10Output
| 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 10Output
| 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 10Output
| 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.
List of related functions
- 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.