Splunk® Cloud Services

SPL2 Search Reference

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

expand command examples

The following are examples for using the SPL2 expand command. To learn more about the expand command, see How the expand command works.

1. Expanding nested arrays

To show how to expand nested arrays, let's use this array, which contains information about famous bridges in Italy and China:

[
   {famous_bridges: 
      [ 
         {name: "Rialto Bridge", length: 157, city: "Venice"}, 
         {name: "Ponte Vecchio Bridge", length: 276, city: "Florence"} 
      ], 
     country: "Italy"
   }, 
   {famous_bridges: 
      [ 
         {name: "Hangzhou Bay Bridge", length: 110880, city:"Jiaxing"}, 
         {name: "Nanpu Bridge", length: 27381, city:"Shanghai"}
      ], 
     country: "China"
   }
  ] 

There is an outer array that contains two objects. Each object contains a set of key-value pairs. The first key is famous_bridges which has as an array as it's value. The second key is country, which has a string as it's value.

Expand the outer array

First you must expand the objects in the outer array.

Use the FROM command with an empty dataset literal to create a timestamp field called _time in the event. Use the SELECT command to specify several fields in the event, including a field called bridges for the array. Add the expand command to separate out the nested arrays by country.

The search to expand the outer array looks like this:

|FROM [{}] SELECT _time, [ {famous_bridges: [{name: "Rialto Bridge", length: 157, city: "Venice"}, {name: "Ponte Vecchio Bridge", length: 276, city: "Florence"}], country: "Italy"}, {famous_bridges: [{name: "Hangzhou Bay Bridge", length: 110880, city:"Jiaxing"}, {name: "Nanpu Bridge", length: 27381, city:"Shanghai"}], country: "China"}] AS bridges | expand bridges

The nested arrays becomes individual arrays. The results look like this:

_time bridges
5/6/2020 2:42:17.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"}
5/6/2020 2:42:17.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"}

The next step would be to flatten the fields in the bridges field.

2. Flattening arrays that have been expanded

You can separate the field-value pairs in the objects into individual fields by using the flatten command.

Let's take the results from the previous example. The results look like this:

_time bridges
5/6/2020 2:42:17.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"}
5/6/2020 2:42:17.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"}

Add the flatten command to the end of the search to flatten the bridges field:

...| flatten bridges

The two keys, famous_bridges and country, become field names. The values for these keys become values for the fields. For country, there is a single value. For famous_bridges, the value is an array of objects.

The results look like this:

_time bridges country famous_bridges
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Italy [{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}]
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} China [{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}]

Expand and flatten the nested array fields

To separate out the details for each bridge, you must expand and flatten the famous_bridges field, which contains the array.

Let's start with expanding the famous_bridges field.

...| flatten bridges | expand famous_bridges

When you expand the famous_bridges field, the results look like this:

_time bridges country famous_bridges
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Italy {"name":"Rialto Bridge","length":157,"city":"Venice"}
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Italy {"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} China {"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"}
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} China {"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}

Then add the flatten command to the end of the search:

...| flatten bridges | expand famous_bridges | flatten famous_bridges

When you flatten the famous_bridges field, the individual key-value pairs in the array are separated out into fields. The results look like this:

When you expand the famous_bridges field, the results look like this:

_time bridges city country famous_bridges length name
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Venice Italy {"name":"Rialto Bridge","length":157,"city":"Venice"} 157 Rialto Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Florence Italy {"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"} 276 Ponte Vecchio Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} Jiaxing China {"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"} 110880 Hangzhou Bay Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} Shanghai China {"name":"Nanpu Bridge","length":27381,"city":"Shanghai"} 27381 Nanpu Bridge

You must expand and flatten each set of arrays. If a field contains four levels of nested arrays, then you must expand and flatten four times.

Removing unwanted fields in the output

When you expand and flatten arrays, especially nested arrays, you can end up with a lot of unnecessary fields in the output.

For example, in this set of results, the bridges and famous_bridges fields are not really necessary. The details from each object have been placed in individual fields:

_time bridges city country famous_bridges length name
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Venice Italy {"name":"Rialto Bridge","length":157,"city":"Venice"} 157 Rialto Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"} Florence Italy {"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"} 276 Ponte Vecchio Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} Jiaxing China {"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"} 110880 Hangzhou Bay Bridge
5/6/2020 3:00:03.000 PM {"famous_bridges":[{"name":"Hangzhou Bay Bridge","length":110880,"city":"Jiaxing"},{"name":"Nanpu Bridge","length":27381,"city":"Shanghai"}],"country":"China"} Shanghai China {"name":"Nanpu Bridge","length":27381,"city":"Shanghai"} 27381 Nanpu Bridge


To remove the unwanted fields, you can add the SELECT command to the end of your search and specify only the fields you want in the output. For example:

......| flatten bridges | expand famous_bridges | flatten famous_bridges | SELECT _time, name, length, city, country

The order that you specify the fields with the SELECT command is the order that the fields appear in the output:

_time name length city country
5/6/2020 3:00:03.000 PM Rialto Bridge 157 Venice Italy
5/6/2020 3:00:03.000 PM Ponte Vecchio Bridge 276 Florence Italy
5/6/2020 3:00:03.000 PM Hangzhou Bay Bridge 110880 Jiaxing China
5/6/2020 3:00:03.000 PM Nanpu Bridge 27381 Shanghai China

See also

expand command
expand command overview
expand command syntax details
expand command usage
Related information
flatten command examples
Last modified on 05 October, 2021
PREVIOUS
expand command usage
  NEXT
fields command overview

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


Was this documentation topic helpful?

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