expand command overview

Use the SPL2 expand command on a field that contains an array of values to produce a separate result row for each object in the array. If there are other fields in the original event, those field values are included in the new rows when the array is expanded.

Syntax

The required syntax is in bold.

expand <object-field>

How the SPL2 expand command works

The SPL2 expand command works on fields that contain arrays.

Consider the following array, which contains two objects with information about famous bridges in London, England:

[
    {name:"Tower Bridge", length:801},
    {name:"Millennium Bridge", length:1066}
]

You can create an event for this array by using several clauses in the from command:

• Use the FROM clause with an empty dataset literal to create an event with the _time field, which contains the timestamp when the event was created.
• Use the SELECT clause to specify expressions. In this example, the expressions are fields in the event, including a field called bridges for the array, and fields called city and country.

The search to create the event looks like this:

| FROM [{}] SELECT _time, [ {name: "Tower Bridge", length: 801}, {name: "Millennium Bridge", length: 1066} ] AS bridges, "London" AS city, "England" AS country

The event looks like this:

_time	bridges	city	country
05 May 2022 2:29:02.000 PM	[{"name":"Tower Bridge","length":801},{"name":"Millennium Bridge","length":1066}]	London	England

Expanding an array

You can separate the objects in the array into individual results by using the expand command:

| FROM [{}] SELECT _time, [ {name: "Tower Bridge", length: 801}, {name: "Millennium Bridge", length: 1066} ] AS bridges, "London" AS city, "England" AS country | expand bridges

The results look like this:

_time	bridges	city	country
05 May 2022 2:29:02.000 PM	{"name":"Tower Bridge","length":801}	London	England
05 May 2022 2:29:02.000 PM	{"name":"Millennium Bridge","length":1066}	London	England

All of the other fields remain unchanged and are duplicated in each result row.

Flattening an object

The expand command is often used with the flatten command.

You can separate the values in the objects into individual fields by using the flatten command:

| FROM [{}] SELECT _time, [ {name: "Tower Bridge", length: 801}, {name: "Millennium Bridge", length: 1066} ] AS bridges, "London" AS city, "England" AS country | expand bridges | flatten bridges

The results look like this:

_time	bridges	city	country	length	name
05 May 2022 2:29:02.000 PM	{"name":"Tower Bridge","length":801}	London	England	801	Tower Bridge
05 May 2022 2:29:02.000 PM	{"name":"Millennium Bridge","length":1066}	London	England	1066	Millennium Bridge

The order of the field names in the output is lexicographical, which is alphabetical and case-sensitive. Internal fields come first, followed by uppercase letters, and finishing with lowercase letters. If you had named the field city instead of City, the city field would appear after the bridges field in the results.

To learn more about lexicographical order, see Lexicographical order in the SPL2 Search Manual.

See also

expand command

expand command syntax details

expand command usage

expand command examples

Related information

Lexicographical order in the SPL2 Search Manual