Skip to main content

ARRAYMERGEELEMENTS Function

Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Array literal reference example:

arraymergeelements(["A","B","C","D"],"-")

Output: Returns the following String value: "A-B-C-D".

Column reference example:

arraymergeelements([myValues)

Output: Generates the new myValuesMergedTogether column containing all of the elements in the arrays in the myElement column joined together without a delimiter between them.

Syntax and Arguments

arraymergeelements(array_ref,my_element, [string_delimiter])

Argument

Required?

Data Type

Description

array_ref

Y

array

Name of Array column, Array literal, or function returning an Array to apply to the function

string_delimiter

Y

string

Optional String delimiter to insert between merged elements in the output String.

For more information on syntax standards, see Language Documentation Syntax Notes.

array_ref

Name of the array column, array literal, or function whose elements you wish to merge.

  • Multiple columns and wildcards are not supported.

Usage Notes:

Required?

Data Type

Example Value

Yes

String (column reference or function) or array literal

myArray1

string_delimiter

Optional string value to insert between elements in the merged output string.

Usage Notes:

Required?

Data Type

Example Value

No

String

"-"

Examples

Dica

For additional examples, see Common Tasks.

Example - Podium Race Finishes

This example illustrates how to generate an Array that is a slice of an another Array, based on index numbers. The elements of this Array can then be merged into a String value.

Functions:

Item

Description

ARRAYSLICE Function

Returns an array containing a slice of the input array, as determined by starting and ending index parameters.

ARRAYMERGEELEMENTS Function

Merges the elements of an array in left to right order into a string. Values are optionally delimited by a provided delimiter.

Source:

The following set of arrays contain results, in order, of a series of races. From this list, the goal is to extract a list of the podium finishers for each race as a single string.

RaceId

RaceResults

1

["racer3","racer5","racer2","racer1","racer6"]

2

["racer6","racer4","racer2","racer1","racer3","racer5"]

3

["racer4","racer3","racer5","racer2","racer6","racer1"]

4

["racer1","racer2","racer3","racer5"]

5

["racer5","racer2","racer4","racer6","racer3"]

Transformation:

From the list of arrays, the first step is to gather the top-3 finishers from each race:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

ARRAYSLICE(RaceResults, 0, 3)

Parameter: New column name

'arrPodium'

The above captures the first three values of the RaceResults arrays into a new set of arrays.

The next step is to merge this new set of arrays into a single string:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

ARRAYMERGEELEMENTS(arrPodium, ',')

Parameter: New column name

'strPodium'

Results:

RaceId

RaceResults

arrPodium

strPodium

1

["racer3","racer5","racer2","racer1","racer6"]

["racer3","racer5","racer2"]

racer3,racer5,racer2

2

["racer6","racer4","racer2","racer1","racer3","racer5"]

["racer6","racer4","racer2"]

racer6,racer4,racer2

3

["racer4","racer3","racer5","racer2","racer6","racer1"]

["racer4","racer3","racer5"]

racer4,racer3,racer5

4

["racer1","racer2","racer3","racer5"]

["racer1","racer2","racer3"]

racer1,racer2,racer3

5

["racer5","racer2","racer4","racer6","racer3"]

["racer5","racer2","racer4"]

racer5,racer2,racer4