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 | |
|---|---|
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 | |
|---|---|
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 |