PARSEARRAY Function
Evaluates a String input against the Array datatype. If the input matches, the function outputs an Array value. Input can be a literal, a column of values, or a function returning String values.
After you have converted your strings to arrays, if a sufficient percentage of input strings from a column are successfully converted to the other data type, the column may be retyped.
ヒント
If the column is not automatically retyped as a result of this function, you can manually set the type to Array in a subsequent recipe step.
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
parsearray(strInput)
Output: Returns the Array data type value for strInput String values.
Syntax and Arguments
parsearray(str_input)
Argument | Required? | Data Type | Description |
|---|---|---|---|
str_input | Y | String | Literal, name of a column, or a function returning String values to match |
For more information on syntax standards, see Language Documentation Syntax Notes.
str_input
Literal, column name, or function returning String values that are to be evaluated for conversion to Array values.
Missing values for this function in the source data result in null values in the output.
Multiple columns and wildcards are not supported.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String | '[1,2,3]' |
Examples
ヒント
For additional examples, see Common Tasks.
Example - parsing strings as an array
Source:
The following table represents raw imported CSV data:
setId | itemsA | itemsB |
|---|---|---|
s01 | "1,2,3" | 4 |
s02 | "2,3,4" | 4 |
s03 | "3,4,5" | 4 |
s04 | "4,5,6" | 4 |
s05 | "5,6,7" | 4 |
s06 | "6,7,8" | 4 |
In the above table, you can see that the two items columns are interpreted differently. In the following steps, you can see how you can parse the data as array values before producing a new column intersecting the two arrays.
Transformation:
Create a new column to store the array version of itemsA:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | itemsA |
Parameter: New column name | arrA |
Remove the quotes from the column:
Transformation Name | |
|---|---|
Parameter: Column | arrA |
Parameter: Find | `"` |
Parameter: Replace with | '' |
Parameter: Match all occurrences | true |
Now create the array by merging the array text value with square brackets and then using the PARSEARRAY function to evaluate the merged value as an array:
Transformation Name | |
|---|---|
Parameter: Columns | arrA |
Parameter: Formula | parsearray(merge(['[',arrA,']'])) |
You can create the second array column using a similar construction in a new column:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | parsearray(merge(['[',itemsB,']'])) |
Parameter: New column name | arrB |
Since both columns have been parsed as array values, you can use the ARRAYINTERSECT function to find the common values:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | arrayintersect([arrA,arrB]) |
Parameter: New column name | arrIntersectAB |
Results:
setId | itemsA | itemsB | arrA | arrB | arrIntersectAB |
|---|---|---|---|---|---|
s01 | "1,2,3" | 4 | [1,2,3] | [4] | [] |
s02 | "2,3,4" | 4 | [2,3,4] | [4] | [4] |
s03 | "3,4,5" | 4 | [3,4,5] | [4] | [4] |
s04 | "4,5,6" | 4 | [4,5,6] | [4] | [4] |
s05 | "5,6,7" | 4 | [5,6,7] | [4] | [] |
s06 | "6,7,8" | 4 | [6,7,8] | [4] | [] |