flatten command: Overview, syntax, and usage

Use the SPL2 flatten command on an object to convert the key-value pairs in the object into separate fields in an event. The flatten command can flatten only the first level of an object, see the Usage section.

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

How the SPL2 flatten command works

The following object contains information about the Tower Bridge in London, England: 

{name: "Tower Bridge", length: 801}

You use the SPL2 flatten command to create separate fields from the key-value pairs in the object.

Consider the following search: 

| FROM [{}] SELECT _time,  {name: "Tower Bridge", length: 801}  as bridges, "London" AS City, "England" AS Country | flatten bridges
  • Using an empty dataset literal with the from command produces a timestamp in the _time field.
  • You specify expressions with the SELECT clause of the from command. The expressions can include the following elements:
    • Objects like {name: "Tower Bridge", length: 801}
    • Values with named fields like "London" AS City
    • Arrays of objects

The results look like this:

_time City Country bridges length name
08 Mar 2022 3:43:48.000 PM London England {"name":"Tower Bridge","length":801} 801 Tower 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 after the bridges field in the results.

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

You can use the flatten command with arrays of objects as well single objects. For additional examples, see flatten command examples.

Syntax

The required syntax is in bold.

flatten <object-field>

Required arguments

object-field

Syntax: <object-field>

Description: The name of the field that contains an object, whose key-value pairs you want to promote into individual fields.

Optional arguments

None.

Usage

Flattening nested objects

The flatten command can flatten only the first level of an object. For example, when an object such {a: {b: 2}} is flattened a field called a is created with a value {b: 2}.

To flatten nested objects, use the expand command with the flatten command.

For an example of how these two commands are used together, see flatten command: Examples and expand command: Examples.

Field order in the output

When you flatten the values in an object, fields are created in the search results. The order of the fields displayed in the output is lexicographical. Lexicographical order means that numbers come before letters and uppercase letters come before lowercase letters.

For example, suppose you have the following object: 

{name: "Akashi-Kaikyo Bridge", length: 12800, city: "Kobe", country: "Japan", Opened: 1998}

When the object is flattened, the order of the fields are rearranged to this:

  • Opened
  • city
  • country
  • length
  • name

The output looks like this:

Opened city country length name
1998 Kobe Japan 12800 Akashi-Kaikyo Bridge

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