flatten command examples

The following examples use the SPL2 flatten command. To learn more about the flatten command, see How the SPL2 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 3:44:57.000 PM	London	England	{"name":"Tower Bridge","length":801}
08 Mar 2022 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
08 Mar 2022 3:43:48.000 PM	London	England	{"name":"Tower Bridge","length":801}	801	Tower Bridge
08 Mar 2022 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
08 Mar 2022 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"}
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
08 Mar 2022 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
21 Apr 2022 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
21 Apr 2022 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
21 Apr 2022 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
21 Apr 2022 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
21 Apr 2022 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
21 Apr 2022 3:00:03.000 PM	cooperative	Forbidden Island	2-4
21 Apr 2022 3:00:03.000 PM	cooperative	Pandemic	2-4
21 Apr 2022 3:00:03.000 PM	cooperative	Sherlock Holmes: Consulting Detective	1-8
21 Apr 2022 2:42:17.000 PM	competitive	Settlers of Catan	3-4
21 Apr 2022 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