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:

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:

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:

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.

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.

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:

This example shows how to use the flatten command in a pipeline.

Consider the following incoming log data:

To separate out the key-value pairs into fields and values, you can use the flatten command.

You can write a pipeline to extract the humidity, temperature, wind, time, and city keys into top-level event fields:

$pipeline = | from $source
| flatten _raw
| into $destination

The resulting events looks like this: