KEYS Function
Extracts the key values from an Object data type column and stores them in an array of String values.
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
Column reference example:
keys('object1')
Output: Returns an array of all of the keys found in the key-value Objects found in the object1 column.
Syntax and Arguments
keys(obj_col)
Argument | Required? | Data Type | Description |
|---|---|---|---|
obj_col | Y | String or Object | Name of column or Object literal whose keys are to be extracted into an array |
For more information on syntax standards, see Language Documentation Syntax Notes.
obj_col
Object literal or name of the Object column whose keys you want to extract into an array.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | Object literal or column reference | myObj |
Examples
Sugerencia
For additional examples, see Common Tasks.
Example - Basic keys example
Source:
Following dataset contains configuration blocks for individual features. These example blocks are of Object type.
Code formatting has been applied to the Object data to improve legibility.
FeatureName | Configuration |
|---|---|
Whiz Widget | {
"enabled": "true",
"maxRows": "1000",
"maxCols": "100"
} |
Magic Button | {
"enabled": "false",
"startDirectory": "/home",
"maxDepth": "15"
} |
Happy Path Finder | {
"enabled": "true"
} |
Transformation:
The following transformation extracts the key values from the Object data in the Configuration column.
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | keys(Configuration) |
Parameter: New column name | 'keys_Configuration' |
Results:
The keys_Configuration column contains the arrays of the key values.
FeatureName | Configuration | keys_Configuration |
|---|---|---|
Whiz Widget | {
"enabled": "true",
"maxRows": "1000",
"maxCols": "100"
} | ["enabled","maxRows","maxCols"] |
Magic Button | {
"enabled": "false",
"startDirectory": "/home",
"maxDepth": "15"
} | ["enabled","startDirectory","maxDepth"] |
Happy Path Finder | {
"enabled": "true"
} | ["enabled"] |
Example - Create an Object of product properties
This example illustrates how to use the ARRAYSTOMAP and KEYS functions to convert values in Array or Object data type of key-value pairs.
Functions:
Item | Description |
|---|---|
ARRAYSTOMAP Function | Combines one array containing keys and another array containing values into an Object of key-value pairs. |
KEYS Function | Extracts the key values from an Object data type column and stores them in an array of String values. |
Source:
Your dataset contains master product data with product properties stored in two arrays of keys and values.
ProdId | ProdCategory | ProdName | ProdKeys | ProdProperties |
|---|---|---|---|---|
S001 | Shirts | Crew Neck T-Shirt | ["type", "color", "fabric", "sizes"] | ["crew","blue","cotton","S,M,L","in stock","padded"] |
S002 | Shirts | V-Neck T-Shirt | ["type", "color", "fabric", "sizes"] | ["v-neck","white","blend","S,M,L,XL","in stock","discount - seasonal"] |
S003 | Shirts | Tanktop | ["type", "color", "fabric", "sizes"] | ["tank","red","mesh","XS,S,M","discount - clearance","in stock"] |
S004 | Shirts | Turtleneck | ["type", "color", "fabric", "sizes"] | ["turtle","black","cotton","M,L,XL","out of stock","padded"] |
Transformation:
When the above data is loaded into the Transformer page, you might need to clean up the two array columns.
Using the following transform, you can map the first element of the first array as a key for the first element of the second, which is its value. You might notice that the number of keys and the number of values are not consistent. For the extra elements in the second array, the default key of ProdMiscProperties is used:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | ARRAYSTOMAP(ProdProperties, ProdValues, 'ProdMiscProperties') |
Parameter: New column name | 'prodPropertyMap' |
You can now use the following steps to generate a new version of the keys:
Transformation Name | |
|---|---|
Parameter: Columns | ProdKeys |
Parameter: Action | Delete selected columns |
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | KEYS(prodPropertyMap) |
Parameter: New column name | 'ProdKeys' |
Results:
ProdId | ProdCategory | ProdName | ProdKeys | ProdProperties | prodPropertyMap |
|---|---|---|---|---|---|
S001 | Shirts | Crew Neck T-Shirt | ["type", "color", "fabric", "sizes","ProdMiscProperties"] | ["crew","blue","cotton","S,M,L","in stock","padded"] | {
"type": [ "crew" ],
"color": [ "blue" ],
"fabric": [ "cotton" ],
"sizes": [ "S,M,L" ],
"ProdMiscProperties": [ "in stock", "padded" ] } |
S002 | Shirts | V-Neck T-Shirt | ["type", "color", "fabric", "sizes","ProdMiscProperties"] | ["v-neck","white","blend","S,M,L,XL","in stock","discount - seasonal"] | {
"type": [ "v-neck" ],
"color": [ "white" ],
"fabric": [ "blend" ],
"sizes": [ "S,M,L,XL" ],
"ProdMiscProperties": [ "in stock", "discount - seasonal" ] } |
S003 | Shirts | Tanktop | ["type", "color", "fabric", "sizes","ProdMiscProperties"] | ["tank","red","mesh","XS,S,M","discount - clearance","in stock"] | {
"type": [ "tank" ],
"color": [ "red" ],
"fabric": [ "mesh" ],
"sizes": [ "XS,S,M" ],
"ProdMiscProperties": [ "discount - clearance", "in stock" ] } |
S004 | Shirts | Turtleneck | ["type", "color", "fabric", "sizes","ProdMiscProperties"] | ["turtle","black","cotton","M,L,XL","out of stock","padded"] | {
"type": [ "turtle" ],
"color": [ "black" ],
"fabric": [ "cotton" ],
"sizes": [ "M,L,XL" ],
"ProdMiscProperties": [ "out of stock", "padded" ] } |