union ↗

Use the union stage to combine the result sets of two queries into a single, unified result set. The stage is invaluable for consolidating data from different sources or different filtered views of the same data.

Syntax

union <datasetname>
union (<inner xql query>)

Parameters

Name Type Required Description
datasetname string Yes (Alternative 1) The name of the existing dataset to combine with the current query's result set.
inner xql query query block Yes (Alternative 2) An inner XQL subquery defined inline, whose results will be combined with the current query's result set.

Returns

The union stage returns a single combined result set containing rows from both the parent query and the joined source (dataset or subquery). All fields from both sources become available to subsequent stages.

Usage notes

  • The union stage does not preserve sort order.
  • If a specific order is required for the final output after the union, you must place a sort stage after the union stage.
  • Combining datasets can be resource-intensive, similar to join operations.
  • Always apply filter stages as early as possible in your queries to reduce the amount of data being processed before the union stage.
  • Utilize the fields stage early to select only necessary columns, minimizing data processing.
  • For very large datasets or complex operations, consider breaking a single, long-running query into multiple smaller queries and potentially saving intermediate results to a new dataset using the target stage.

Examples

Example 1: Union with a named dataset

Goal: Combines events from the sample_xql_raw dataset that match "File access attempt" with a conceptually created successful_logins dataset (containing "User login successful" events).

XQL code:

dataset = sample_xql_raw
| filter event_description = "File access attempt"
| fields event_id, event_description, is_successful
| union successful_logins // Assumes 'successful_logins' dataset exists

Explanation: The query filters the current dataset for file access attempts and then uses union to combine these results with all records from the existing successful_logins dataset.

Output:

event_id event_description is_successful
102 File access attempt false
101 User login successful true

Example 2: Union with an Inner XQL Query

Goal: Combines records from sample_xql_raw where is_successful is false with records where duration_seconds is greater than 10.0, derived from a separate inner subquery.

XQL code:

dataset = sample_xql_raw
| filter is_successful = false
| fields event_id, event_description, is_successful, duration_seconds
| union (
    dataset = sample_xql_raw
    | filter duration_seconds > 10.0
    | fields event_id, event_description, is_successful, duration_seconds
)

Explanation: The main query retrieves unsuccessful events. The union stage executes an inner subquery to retrieve events with a duration greater than 10.0 seconds. The results of both queries are combined into a single output.

Output:

event_id event_description is_successful duration_seconds
102 File access attempt false 0.8
106 Unauthorized access detected false 2.1
109 API request throttled false 0.05
103 Network connection established true 10.2
108 Software update initiated true 15.3
110 Database backup completed true 60.0