Splunk® Cloud Services

SPL2 Search Reference

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
Last modified on 31 January, 2024
eventstats command examples   expand command syntax details

This documentation applies to the following versions of Splunk® Cloud Services: current


Was this topic useful?







You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters