Splunk® Cloud Services

SPL2 Search Reference

Acrobat logo Download manual as PDF


Acrobat logo Download topic as PDF

flatten command examples

The following examples use the SPL2 flatten command. To learn more about the flatten command, see How the flatten command works.

The flatten command is often used with the expand command when you want to flatten arrays or nested objects.

1. Flatten individual objects

You can flatten a field that contains a single object of key-value pairs.

Consider the following search:

| FROM [{}] SELECT _time, {name: "Helix Bridge", length: 918, country: "Singapore"} as bridges

This search uses 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, such as individual objects like {name: "Helix Bridge", length: 918, country: "Singapore"}.

The result looks like this:

_time bridges
5/1/2020 3:45:25.000 PM {name: "Helix Bridge", length: 918, country: "Singapore"}

When you add the flatten command, you must specify the field to flatten:

| FROM [{}] SELECT _time, {name: "Helix Bridge", length: 918, country: "Singapore"} as bridges | flatten bridges

The result looks like this:

_time bridges country length name
5/1/2020 3:45:25.000 PM {name: "Helix Bridge", length: 918, country: "Singapore"} Singapore 918 Helix Bridge

2. Flattening arrays

The following array contains two objects with information about bridges in London, England.

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

Consider the following search:

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

This search uses 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. The expressions can include the following elements:
    • Arrays of objects like [ {name: "Tower Bridge", length: 801}, {name: "Millennium Bridge", length: 1066} ]
    • Individual objects like {name: "Tower Bridge", length: 801}
    • Values with named fields like "London" AS City

The results look like this:

_time City Country bridges
5/1/2020 3:45:25.000 PM London England [{"name":"Tower Bridge","length":801},{"name":"Millennium Bridge","length":1066}]

Expand arrays into objects

Before you can use the flatten command, you must first expand the array into separate objects.

This is the search:

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

Each object is separated into its own search result. The results look like this:

_time City Country bridges
5/1/2020 3:44:57.000 PM London England {"name":"Tower Bridge","length":801}
5/1/2020 3:44:57.000 PM London England {"name":"Millennium Bridge","length":1066}

Flatten objects into fields

You can use the flatten command to create separate fields from the key-value pairs in the objects:

| 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 City Country bridges length name
5/1/2020 3:43:48.000 PM London England {"name":"Tower Bridge","length":801} 801 Tower Bridge
5/1/2020 3:43:48.000 PM London England {"name":"Millennium Bridge","length":1066} 1066 Millennium Bridge

3. Flatten nested objects

To show how to flatten nested arrays, let's use this array, which contains information about popular board games:

[
   {games: 
      [ 
         {name: "Forbidden Island", players: "2-4"}, 
         {name: "Pandemic", players: "2-4"},
         {name: "Sherlock Holmes: Consulting Detective", players: "1-8"}
      ], 
     type: "cooperative"
   }, 
   {games: 
      [ 
         {name: "Settlers of Catan", players: "3-4"}, 
         {name: "Ticket to Ride", players: "2-5"}
      ], 
     type: "competitive"
   }
] 

There is an outer array that contains two objects. Each object contains a set of key-value pairs. The first key is games which has an array as its value. The second key is type, which has a string as its value.

Expand the outer array

First you must expand the objects in the outer array using a search that looks like this:

| FROM [{}] SELECT _time, [{games: [ {name: "Forbidden Island", players: "2-4"}, {name: "Pandemic", players: "2-4"},{name: "Sherlock Holmes: Consulting Detective", players: "1-8"}], type: "cooperative"}, {games: [{name: "Settlers of Catan", players: "3-4"}, {name: "Ticket to Ride", players: "2-5"}], type: "competitive"}] AS boardgames | expand boardgames

The outer objects become individual events. The results look like this:

_time boardgames
5/6/2020 2:42:17.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"}
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"}

Flatten to separate the objects

You must flatten the fields, games and type, out of the outer objects.

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

...| flatten boardgames

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

The results look like this:

_time boardgames games type
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} [{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}] cooperative
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} [{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}] competitive

Expand and flatten the nested array fields

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

Start with expanding the games field.

...| flatten boardgames | expand games

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

_time boardgames games type
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Forbidden Island","players":"2-4"} cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Pandemic","players":"2-4"} cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Sherlock Holmes: Consulting Detective","players":"1-8"} cooperative
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Settlers of Catan","players":"3-4"} competitive
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Ticket to Ride","players":"2-5"} competitive

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

...| flatten boardgames | expand games | flatten games

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

_time boardgames games names players type
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Forbidden Island","players":"2-4"} Forbidden Island 2-4 cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Pandemic","players":"2-4"} Pandemic 2-4 cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Sherlock Holmes: Consulting Detective","players":"1-8"} Sherlock Holmes: Consulting Detective 1-8 cooperative
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Settlers of Catan","players":"3-4"} Settlers of Catan 3-4 competitive
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Ticket to Ride","players":"2-5"} Ticket to Ride 2-5 competitive

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.

4. 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 boardgames and games fields are not really necessary. The details from each object have been placed in individual fields for name, players, and type of game.

_time boardgames games name players type
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Forbidden Island","players":"2-4"} Forbidden Island 2-4 cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Pandemic","players":"2-4"} Pandemic 2-4 cooperative
5/6/2020 3:00:03.000 PM {"games":[{"name":"Forbidden Island","players":"2-4"},{"name":"Pandemic","players":"2-4"},{"name":"Sherlock Holmes: Consulting Detective","players":"1-8"}],"type":"cooperative"} {"name":"Sherlock Holmes: Consulting Detective","players":"1-8"} Sherlock Holmes: Consulting Detective 1-8 cooperative
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Settlers of Catan","players":"3-4"} Settlers of Catan 3-4 competitive
5/6/2020 2:42:17.000 PM {"games":[{"name":"Settlers of Catan","players":"3-4"},{"name":"Ticket to Ride","players":"2-5"}],"type":"competitive"} {"name":"Ticket to Ride","players":"2-5"} Ticket to Ride 2-5 competitive

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

...| SELECT _time, type, name, players

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

_time type name players
5/6/2020 3:00:03.000 PM cooperative Forbidden Island 2-4
5/6/2020 3:00:03.000 PM cooperative Pandemic 2-4
5/6/2020 3:00:03.000 PM cooperative Sherlock Holmes: Consulting Detective 1-8
5/6/2020 2:42:17.000 PM competitive Settlers of Catan 3-4
5/6/2020 2:42:17.000 PM competitive Ticket to Ride 2-5

See also

flatten command
flatten command overview
flatten command syntax details
flatten command usage
expand command
expand command examples
Last modified on 16 July, 2020
PREVIOUS
flatten command usage
  NEXT
from 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