Skip to main content
The union operator in APL allows you to combine the results of two or more queries into a single output. The operator is useful when you need to analyze or compare data from different datasets or tables in a unified manner. By using union, you can merge multiple sets of records, keeping all data from the source tables without applying any aggregation or filtering. The union operator is particularly helpful in scenarios like log analysis, tracing OpenTelemetry events, or correlating security logs across multiple sources. You can use it to perform comprehensive investigations by bringing together information from different datasets into one query.

​
Union of two datasets

To understand how the union operator works, consider these datasets: Server requests
_timestatusmethodtrace_id
12:10200GET1
12:15200POST2
12:20503POST3
12:25200POST4
App logs
_timetrace_idmessage
12:121foo
12:213bar
13:3527baz
Performing a union on Server requests and Application logs would result in a new dataset with all the rows from both DatasetA and DatasetB. A union of requests and logs would produce the following result set:
_timestatusmethodtrace_idmessage
12:10200GET1
12:121foo
12:15200POST2
12:20503POST3
12:213bar
12:25200POST4
13:3527baz
This result combines the rows and merges types for overlapping fields.

​
For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.
In Splunk SPL, the append command works similarly to the union operator in APL. Both operators are used to combine multiple datasets. However, while append in Splunk typically adds one dataset to the end of another, APL’s union merges datasets while preserving all records.
index=web OR index=security
In ANSI SQL, the UNION operator performs a similar function to the APL union operator. Both are used to combine the results of two or more queries. However, SQL’s UNION removes duplicates by default, whereas APL’s union keeps all rows unless you use union with=kind=unique.
SELECT * FROM web_logs
UNION
SELECT * FROM security_logs;

​
Usage

​
Syntax

T1 | union [withsource=FieldName] [T2], [T3], ...

​
Parameters

  • T1, T2, T3, ...: Tables or query results you want to combine into a single output.
  • withsource: Optional, adds a field to the output where each value specifies the source dataset of the row. Specify the name of this additional field in FieldName.

​
Returns

The union operator returns all rows from the specified tables or queries. If fields overlap, they are merged. Non-overlapping fields are retained in their original form.

​
Use case examples

  • Log analysis
  • OpenTelemetry traces
  • Security logs
In log analysis, you can use the union operator to combine HTTP logs from different sources, such as web servers and security systems, to analyze trends or detect anomalies.Query
['sample-http-logs']
| union ['security-logs']
| where status == '500'
Output
_timeidstatusurimethodgeo.citygeo.countryreq_duration_ms
2024-10-17 12:34:56user123500/api/loginGETLondonUK345
2024-10-17 12:35:10user456500/api/update-profilePOSTBerlinGermany123
This query combines two datasets (HTTP logs and security logs) and filters the combined data to show only those entries where the HTTP status code is 500.

​
Other examples

​
Basic union

This example combines all rows from github-push-event and github-pull-request-event without any transformation or filtering. 
['github-push-event']
| union ['github-pull-request-event']
Run in Playground

​
Filter after union

This example combines the datasets, and then filters the data to only include rows where the method is GET. 
['sample-http-logs']
| union ['github-issues-event']
| where method == "GET"
Run in Playground

​
Aggregate after union

This example combines the datasets and summarizes the data, counting the occurrences of each combination of content_type and actor. 
['sample-http-logs']
| union ['github-pull-request-event']
| summarize Count = count() by content_type, actor
Run in Playground

​
Filter and project specific data from combined log sources

This query combines GitHub pull request event logs and GitHub push events, filters by actions made by github-actions[bot], and displays key event details such as time, repository, commits, head , id. 
['github-pull-request-event']
| union ['github-push-event']
| where actor == "github-actions[bot]"
| project _time, repo, ['id'], commits, head
Run in Playground

​
Union with field removing

This example removes the content_type and commits field in the datasets sample-http-logs and github-push-event before combining the datasets. 
['sample-http-logs']
| union ['github-push-event']
| project-away content_type, commits
Run in Playground

​
Filter after union

This example performs a union and then filters the resulting set to only include rows where the method is GET. 
['sample-http-logs']
| union ['github-issues-event']
| where method == "GET"
Run in Playground

​
Union with order by

After the union, the result is ordered by the type field. 
['sample-http-logs']
| union hn
| order by type
Run in Playground

​
Union with joint conditions

This example performs a union and then filters the resulting dataset for rows where content_type contains the letter a and city is seattle. 
['sample-http-logs']
| union ['github-pull-request-event']
| where content_type contains "a" and ['geo.city']  == "Seattle"
Run in Playground

​
Union and count unique values

After the union, the query calculates the number of unique geo.city and repo entries in the combined dataset. 
['sample-http-logs']
| union ['github-push-event']
| summarize UniqueNames = dcount(['geo.city']), UniqueData = dcount(repo)
Run in Playground

​
Union using withsource

The example below returns the union of all datasets that match the pattern github* and counts the number of events in each. 
union withsource=dataset github*
| summarize count() by dataset
Run in Playground

​
Best practices for the union operator

To maximize the effectiveness of the union operator in APL, here are some best practices to consider:
  • Before using the union operator, ensure that the fields being merged have compatible data types.
  • Use project or project-away to include or exclude specific fields. This can improve performance and the clarity of your results, especially when you only need a subset of the available data.
⌘I