Working with Objects

This section describes how to work with the Object data type.Anobject(ormap) is a set of key-value pairs.

Any individual value can contain another set of key-value pairs, which enables the creation of nested data objects.

Tip

As one of its values, an object can contain an array, which can in turn contain other objects or arrays. In this manner, you can created nested hybrid data objects by combining these two data types.

Structure of Objects

An Object data type is a method for encoding key-value pairs. A single field value may contain one or more sets of key-value pairs. A simple example:

{"Texas":"TX"},
{"New York":"NY"},
{"California":"CA"},

Notes:

The above example features repeated data in a repeated format across each line.
Effectively, these are records of data, mapping a state's formal name (e.g. Texas) to its two-letter abbreviation (e.g. TX).
Data structures of the Object data type can be more complex.

Note

The Cloud Portal can recognize up to 250 unique keys in a column of Object data type.

Import Objects

Import Object columns

When a column is identified as a set of key-value pairs during import, the column may be typed as an Object data type column. These key-value pairs can be extracted and converted into rows and columns in the dataset using transformations and functions in the application.

Import JSON files

The Object data type can be the basis for entire JSON files. When JSON files are formatted in a way that can be parsed by the the Cloud Portal, they can be converted into tabular format as part of the import process. If the preceding example is the entire file, the conversion process may display the dataset in the Transformer page as the following:

column1	column2
Texas	TX
New York	NY
California	CA

For more information, see Working with JSON v2.

Create Objects

Within the Cloud Portal, you can use functions and transformations to create columns that are recognized as Object data type.

Create by nesting

You can nest multiple columns into a single column of objects using the nest transform.

This section provides a simple example of nesting columns into a new column of Object data type.

Source:

In the following example, furniture product dimensions are stored in separate columns in cm.

Category	Name	Length_cm	Width_cm	Height_cm
bench	Hooska	118.11	74.93	46.34
lamp	Tansk	30.48	30.48	165.1
bookshelf	Brock	27.94	160.02	201.93
couch	Loafy	95	227	83

Transformation:

Use the nest transform to bundle the data into a single column.

Transformation Name	`Nest columns into Objects`
Parameter: Columns	Length_cm,Width_cm,Height_cm
Parameter: Nest columns to	Object
Parameter: New column name	'Dimensions'

Results:

Category	Name	Length_cm	Width_cm	Height_cm	Dimensions
bench	Hooska	118.11	74.93	46.34	{"Length_cm":"118.11","Width_cm":"74.93","Height_cm":"46.34"}
lamp	Tansk	30.48	30.48	165.1	{"Length_cm":"30.48","Width_cm":"30.48","Height_cm":"165.1"}
bookshelf	Brock	27.94	160.02	201.93	{"Length_cm":"27.94","Width_cm":"160.02","Height_cm":"201.93"}
couch	Loafy	95	227	83	{"Length_cm":"95,"Width_cm":"227","Height_cm":"83"}

Create by Filtering Strings

You can create objects by filtering strings by using the FILTEROBJECT function.

You can create nested objects by filtering strings. In this example, column headers and column values are nested into a single entity in a new column of Object data type.

Functions:

Item	Description
FILTEROBJECT Function	Filters the keys and values from an Object data type column based on a specified key value.
PARSEOBJECT Function	Evaluates a String input against the Object datatype. If the input matches, the function outputs an Object value. Input can be a literal, a column of values, or a function returning String values.

Source:

The following table shows a series of requests for inventory on three separate products. These are rolling requests, so inventory levels in the subsequent request are decreased based on the previous request.

date	reqProdId	reqValue	prodA	prodB	prodC
5/10/21	prodA	10	90	100	100
5/10/21	prodC	20	90	100	80
5/10/21	prodA	15	75	100	80
5/11/21	prodB	25	75	75	80
5/11/21	prodA	5	70	75	80
5/11/21	prodC	30	70	75	50
5/12/21	prodB	10	70	65	50

You must create a column containing the request information and the inventory level information for the requested product after the request has been fulfilled.

Transformation:

The five data columns must be nested into an Object. The generated column is called inventoryLevels.

Transformation Name	`Nest columns into Objects`
Parameter: Columns	reqProdId,reqValue,prodA,prodB,prodC
Parameter: Nest columns to	Object
Parameter: New column name	inventoryLevels

You can then build the inventory response column (inventoryResponse) using the FILTEROBJECT function:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	filterobject(parseobject(inventoryRequest), ['reqProdId','reqValue',reqProdId])
Parameter: New column name	inventoryResponse

Results:

The inventoryResponse column contains the request information and the response information after the request has been fulfilled.

date	reqProdId	reqValue	prodA	prodB	prodC	inventoryLevels	inventoryResponse
5/10/21	prodA	10	90	100	100	{"reqProdId":"prodA","reqValue":"10","prodA":"90","prodB":"100","prodC":"100"}	{"reqProdId":"prodA","reqValue":"10","prodA":"90"}
5/10/21	prodC	20	90	100	80	{"reqProdId":"prodC","reqValue":"20","prodA":"90","prodB":"100","prodC":"80"}	{"reqProdId":"prodC","reqValue":"20","prodC":"80"}
5/10/21	prodA	15	75	100	80	{"reqProdId":"prodA","reqValue":"15","prodA":"75","prodB":"100","prodC":"80"}	{"reqProdId":"prodA","reqValue":"15","prodA":"75"}
5/11/21	prodB	25	75	75	80	{"reqProdId":"prodB","reqValue":"25","prodA":"75","prodB":"75","prodC":"80"}	{"reqProdId":"prodB","reqValue":"25","prodB":"75"}
5/11/21	prodA	5	70	75	80	{"reqProdId":"prodA","reqValue":"5","prodA":"70","prodB":"75","prodC":"80"}	{"reqProdId":"prodA","reqValue":"5","prodA":"70"}
5/11/21	prodC	30	70	75	50	{"reqProdId":"prodC","reqValue":"30","prodA":"70","prodB":"75","prodC":"50"}	{"reqProdId":"prodC","reqValue":"30","prodC":"50"}
5/12/21	prodB	10	70	65	50	{"reqProdId":"prodB","reqValue":"10","prodA":"70","prodB":"65","prodC":"50"}	{"reqProdId":"prodB","reqValue":"10","prodB":"65"}

Convert from Arrays

You can create objects by converting two arrays of key value pairs by using the the ARRAYSTOMAP function.

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	`New formula`
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	`Delete columns`
Parameter: Columns	ProdKeys
Parameter: Action	Delete selected columns

Transformation Name	`New formula`
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" ] }

Read from Objects

When a column is recognized as an Object data type, you can apply transformations to extract the keys, the values, or both from the column for use in a new column. You can use pattern-based matching to acquire the values of interest for further analysis or cleaning.

Extract Keys

You can extract keys from objects from the Object data and and stores them in an array of String values.

You can extract the keys from an Object column into an Array of String values.

Functions:

Item	Description
KEYS Function	Extracts the key values from an Object data type column and stores them in an array of String values.

Source:

The following dataset contains configuration blocks for individual features, each of which has a different configuration. These example blocks are of Object type.

Tip

In the following example configuration, the keys are the values on the left (e.g. enabled, maxRows, and maxCols), while the values for those keys are on the right side.

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 keys from the Object data in the Configuration column.

Transformation Name	`New formula`
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"]

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

Extract Object Values

You can extract an object's values in to a new column.

This simple example demonstrates how to extract nested values from Object elements into a separate column.

Source:

For example, suppose your restaurant dataset includes a set of characteristics in the restFeatures column in the following JSON format, from which you are interested in the total number of seats in the restaurant.

This example contains the data for a single restaurant, formatted as regular JSON, for simplicity:

{
  "Credit":"Y",
  "Accessible":"Y",
  "Restrooms":"Y",
  "EatIn":"Y",
  "ToGo":"N",
  "AlcoholBeer":"Y",
  "AlcoholHard":"N",
  "TotalTables":"10",
  "TotalTableSeats":"36",
  "Counter":"Y",
  "CounterSeats":"8"
}

Transformation:

You can use the following transformation to extract the values from TotalTableSeats and CounterSeats into separate columns:

Note

Change the column type to Object before applying the following transformation.

Note

Each key must be entered on a separate line in the Path to elements area.

Transformation Name	`Unnest Objects into columns`
Parameter: Column	restFeatures
Parameter: Paths to elements	TotalTableSeats
Parameter: Paths to elements	CounterSeats
Parameter: Include original column name	Selected

Results:

restFeatures_TotalTable Seats	restFeatures_CounterSeats
36	8

After converting into separate columns, you can perform a simple sum of the TotalTableSeats and CounterSeats columns to determine the total number of seats in the restaurant.

The final table looks like the following:

restFeatures_TotalTable Seats	restFeatures_CounterSeats	TotalSeats_Restaurant
36	8	44

Convert to String

Depending on the use, it may be easier to work with your objects as String values. While Strings have no inherent structure, they do have a wide range of functions that you can use to find and extract information from the values. Some useful functions:

Function	Description
FIND Function	Returns the index value in the input string where a specified matching string is located in provided column, string literal, or function returning a string. Search is conducted left-to-right.
RIGHTFIND Function	Returns the index value in the input string where the last instance of a matching string is located. Search is conducted right-to-left.
FINDNTH Function	Returns the position of the nth occurrence of a letter or pattern in the input string where a specified matching string is located in the provided column. You can search either from left or right.
SUBSTITUTE Function	Replaces found string literal or pattern or column with a string, column, or function returning strings.

Unnest Objects

You can unnest the object data type to create new rows or columns based on the keys in the source data. The following example shows how to unnest object values into separate columns.

This example shows how you can unpack data nested in an Object into separate columns.

Source:

You have the following information on used cars. The VIN column contains vehicle identifiers, and the Properties column contains key-value pairs describing characteristics of each vehicle. You want to unpack this data into separate columns.

VIN	Properties
XX3 JT4522	year=2004,make=Subaru,model=Impreza,color=green,mileage=125422,cost=3199
HT4 UJ9122	year=2006,make=VW,model=Passat,color=silver,mileage=102941,cost=4599
KC2 WZ9231	year=2009,make=GMC,model=Yukon,color=black,mileage=68213,cost=12899
LL8 UH4921	year=2011,make=BMW,model=328i,color=brown,mileage=57212,cost=16999

Transformation:

Add the following transformation, which identifies all of the key values in the column as beginning with alphabetical characters.

The valueafter string identifies where the corresponding value begins after the key.
The delimiter string indicates the end of each key-value pair.

Transformation Name	`Convert keys/values into Objects`
Parameter: Column	Properties
Parameter: Key	`{alpha}+`
Parameter: Separator between key and value	`=`
Parameter: Delimiter between pair	','

Now that the Object of values has been created, you can use the unnest transform to unpack this mapped data. In the following, each key is specified, which results in separate columns headed by the named key:

Note

Each key must be entered on a separate line in the Path to elements area.

Transformation Name	`Unnest Objects into columns`
Parameter: Column	extractkv_Properties
Parameter: Paths to elements	year
Parameter: Paths to elements	make
Parameter: Paths to elements	model
Parameter: Paths to elements	color
Parameter: Paths to elements	mileage
Parameter: Paths to elements	cost

Results:

When you delete the unnecessary Properties columns, the dataset now looks like the following:

VIN	year	make	model	color	mileage	cost
XX3 JT4522	2004	Subaru	Impreza	green	125422	3199
HT4 UJ9122	2006	VW	Passat	silver	102941	4599
KC2 WZ9231	2009	GMC	Yukon	black	68213	12899
LL8 UH4921	2011	BMW	328i	brown	57212	16999

In this section:

Working with Objects

Structure of Objects

Import Objects

Import Object columns

Import JSON files

Create Objects

Create by nesting

Create by Filtering Strings

Convert from Arrays

Read from Objects

Extract Keys

Extract Object Values

Convert to String

Unnest Objects

Search results