column_ifexists
This page explains how to use the column_ifexists function in APL.
Use column_ifexists() to make your queries resilient to schema changes. The function checks if a field with a given name exists in the dataset. If it does, the function returns it. If not, it returns a fallback field or expression that you provide.
This is especially useful when working with datasets that evolve over time or come from multiple sources with different schemas. Instead of failing when a field is missing, your query continues running by using a default. Use this function to safely handle queries where the presence of a field isn’t guaranteed.
Usage
Syntax
column_ifexists(FieldName, DefaultValue)Parameters
FieldName: The name of the field to return as a string.DefaultValue: The fallback value to return ifFieldNamedoesn’t exist. This can be another field or a literal.
Returns
Returns the field specified by FieldName if it exists in the table schema. Otherwise, returns the result of DefaultValue.
Use case examples
You want to examine HTTP logs, and your schema might have a geo.region field in some environments and not in others. You fall back to geo.country when geo.region is missing.
Query
['sample-http-logs']
| project _time, location = column_ifexists('geo.region', ['geo.country'])Output
| _time | location |
|---|---|
| 2025-04-28T12:04:10Z | United States |
| 2025-04-28T12:04:12Z | Canada |
| 2025-04-28T12:04:15Z | United Kingdom |
The query returns geo.region if it exists; otherwise, it falls back to geo.country.
You analyze OpenTelemetry traces and you’re not sure if your data contains status_code and status fields. You fall back to 100 when it’s missing.
Query
['otel-demo-traces']
| extend status_code_field = column_ifexists('status_code', '100')
| extend status_field = column_ifexists('status', 100)
| project _time, trace_id, span_id, status_code_field, status_fieldOutput
| _time | trace_id | span_id | status_code_field | status_field |
|---|---|---|---|---|
| 2025-04-28T10:30:12Z | abc123 | span567 | nil | 100 |
| 2025-04-28T10:30:15Z | def456 | span890 | 200 | 100 |
The query returns the status_code field if it exists. Otherwise, it falls back to 100.
You inspect logs for suspicious activity. In some datasets, a threat_level field exists, but not in all. You use the status field as a fallback.
Query
['sample-http-logs']
| project _time, id, threat = column_ifexists('threat_level', status)Output
| _time | id | threat |
|---|---|---|
| 2025-04-28T13:22:11Z | u123 | 200 |
| 2025-04-28T13:22:13Z | u456 | 403 |
The function avoids breaking the query if threat_level doesn’t exist by defaulting to status.
List of related functions
- coalesce: Returns the first non-null value from a list of expressions. Use when you want to handle null values, not missing fields.
- iff: Performs conditional logic based on a boolean expression. Use when you want explicit control over evaluation.
- isnull: Checks if a value is null. Useful when combined with other functions for fine-grained control.
- case: Allows multiple conditional branches. Use when fallback logic depends on multiple conditions.
- project: Selects and transforms fields. Use with
column_ifexists()to build resilient field projections.