Axiom
Overview

column_ifexists

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 if FieldName doesn’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'])

Run in Playground

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_field

Run in Playground

Output

_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)

Run in Playground

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.

  • 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.

Other query languages#

Splunk SPL users

In Splunk, field selection is strict—missing fields typically return null in results, but conditional logic for fallback fields requires using eval or coalesce. In APL, column_ifexists() directly substitutes the fallback field at query-time based on schema.

Splunk example

... | eval field=if(isnull(Capital), State, Capital)

APL equivalent

StormEvents | project column_ifexists('Capital', State)
ANSI SQL users

In SQL, you need to check for the existence of a field using system views or error handling. column_ifexists() in APL simplifies this by allowing fallback behavior inline without needing procedural code.

SQL example

SELECT CASE
  WHEN EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_NAME = 'StormEvents' AND COLUMN_NAME = 'Capital')
  THEN Capital ELSE State END AS Result
FROM StormEvents

APL equivalent

StormEvents | project column_ifexists('Capital', State)

Updated

Was this page helpful?