union command overview

The SPL2 union command merges the results from two or more datasets into one larger dataset. One of the datasets can be the incoming search results that are then piped into the union command and merged with a second dataset.

If all of the datasets that you want to merge are indexes, you can use the indexes dataset function instead of the union command. See indexes dataset function.

Syntax

The required syntax is in bold.

union

<dataset> ["," <dataset>...]

How the SPL2 union command works

You can use the SPL2 union command to merge datasets that have identical or different field names. The following sections illustrate how to use the union command in both of these situations.

Datasets with identical field names

Consider the following two datasets:

products-amer

productID	product_name	supplierID	supplier_name	categoryID
BS-AG-G09	Benign Space Debris	A51G-USA	Area 51 Games	ARCADE
SF-BVS-G01	Grand Theft Scooter	IP-PAN	Isthmus Pastimes	ARCADE

products-apac

productID	product_name	supplierID	supplier_name	categoryID
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY
PZ-SG-G05	Puppies vs. Zombies	TF-JAP	Tiger Fun	STRATEGY
SC-MG-G10	SIM Cubicle	PMG-KOR	Play More Games	SIMULATION

You can use the SPL2 union command to bring these dataset together. For example:

$products = union products-amer, products-apac

The results look something like this:

productID	product_name	supplierID	supplier_name	categoryID
BS-AG-G09	Benign Space Debris	A51G-USA	Area 51 Games	ARCADE
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY
PZ-SG-G05	Puppies vs. Zombies	TF-JAP	Tiger Fun	STRATEGY
SC-MG-G10	SIM Cubicle	PMG-KOR	Play More Games	SIMULATION
SF-BVS-G01	Grand Theft Scooter	IP-PAN	Isthmus Pastimes	ARCADE

Datasets with different field names

Consider the following events from two datasets:

products-apac

productID	product_name	supplierID	supplier_name	categoryID
DC-SG-G02	Dream Crusher	PMG-KOR	Play More Games	STRATEGY

suppliers_apac

supplierId	supplier_name	contact_name	email	address
PMG-KOR	Play More Games	Vanya Patel	vanya@sample.com	234 Sejong-daero ... Seoul South Korea

Notice that both events have a field called supplier_name and fields for the supplier ID, but with different capitalization: supplierID and supplierId.

You can use the union command to bring these dataset together. For example:

$products = union products-apac, suppliers_apac

When the datasets are unioned, the fields from both datasets added to the output. The NULL value is added to fields that were not in the original event.

The results look something like this:

address	categoryID	contact_name	email	productID	product_name	supplierID	supplierId	supplier_name
NULL	STRATEGY	NULL	NULL	DC-SG-G02	Dream Crusher	PMG-KOR	NULL	Play More Games
234 Sejong-daero ... Seoul South Korea	NULL	Vanya Patel	vanya@sample.com	NULL	NULL	NULL	PMG-KOR	Play More Games

Both events have a field called supplier_name that appears in the output with the same value. However, because the supplier ID fields have different capitalization, both fields appear in the output, even though the fields have the same value.

See also

union command

union command syntax details

union command usage

union command examples