expand command: Overview, syntax, and usage

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.

Use these links to quickly navigate to the main sections in this topic:

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

Note: 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 before the bridges field in the results.

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

Syntax

The required syntax is in bold.

expand <object-field>

Required arguments

object-field

Syntax: <object-field>

Description: The name of the field that contains the array of objects that you want to expand.

Optional arguments

None.