expand command examples

The following are examples for using the SPL2 expand command. To learn more about the expand command, see How the SPL2 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 | eval _time = now() | expand bridges

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

_time	bridges
13 Apr 2022 2:42:17.000 PM	{"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"}
13 Apr 2022 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
13 Apr 2022 2:42:17.000 PM	{"famous_bridges":[{"name":"Rialto Bridge","length":157,"city":"Venice"},{"name":"Ponte Vecchio Bridge","length":276,"city":"Florence"}],"country":"Italy"}
13 Apr 2022 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
13 Apr 2022 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"}]
13 Apr 2022 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
13 Apr 2022 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"}
13 Apr 2022 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"}
13 Apr 2022 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"}
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 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
13 Apr 2022 3:00:03.000 PM	Rialto Bridge	157	Venice	Italy
13 Apr 2022 3:00:03.000 PM	Ponte Vecchio Bridge	276	Florence	Italy
13 Apr 2022 3:00:03.000 PM	Hangzhou Bay Bridge	110880	Jiaxing	China
13 Apr 2022 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