Skip to main content

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

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