Page tree

Release 9.2


Contents:

   

Contents:


This section describes how to work with the Object data type. An object (or map) 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 Designer Cloud application 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  Designer Cloud application , 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:

column1column2
TexasTX
New YorkNY
CaliforniaCA

For more information, see Working with JSON v2.

Create Objects

Within the  Designer Cloud application , 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.

CategoryNameLength_cmWidth_cmHeight_cm
benchHooska118.1174.9346.34
lampTansk30.4830.48

165.1

bookshelfBrock27.94160.02201.93
couchLoafy9522783

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:

CategoryNameLength_cmWidth_cmHeight_cmDimensions
benchHooska118.1174.9346.34{"Length_cm":"118.11","Width_cm":"74.93","Height_cm":"46.34"}
lampTansk30.4830.48

165.1

{"Length_cm":"30.48","Width_cm":"30.48","Height_cm":"165.1"}
bookshelfBrock27.94160.02201.93{"Length_cm":"27.94","Width_cm":"160.02","Height_cm":"201.93"}
couchLoafy9522783{"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:

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

datereqProdIdreqValueprodAprodBprodC
5/10/21prodA1090100100
5/10/21prodC209010080
5/10/21prodA157510080
5/11/21prodB25757580
5/11/21prodA5707580
5/11/21prodC30707550
5/12/21prodB10706550

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.

datereqProdIdreqValueprodAprodBprodCinventoryLevelsinventoryResponse
5/10/21prodA1090100100{"reqProdId":"prodA","reqValue":"10","prodA":"90","prodB":"100","prodC":"100"}{"reqProdId":"prodA","reqValue":"10","prodA":"90"}
5/10/21prodC209010080{"reqProdId":"prodC","reqValue":"20","prodA":"90","prodB":"100","prodC":"80"}{"reqProdId":"prodC","reqValue":"20","prodC":"80"}
5/10/21prodA157510080{"reqProdId":"prodA","reqValue":"15","prodA":"75","prodB":"100","prodC":"80"}{"reqProdId":"prodA","reqValue":"15","prodA":"75"}
5/11/21prodB25757580{"reqProdId":"prodB","reqValue":"25","prodA":"75","prodB":"75","prodC":"80"}{"reqProdId":"prodB","reqValue":"25","prodB":"75"}
5/11/21prodA5707580{"reqProdId":"prodA","reqValue":"5","prodA":"70","prodB":"75","prodC":"80"}{"reqProdId":"prodA","reqValue":"5","prodA":"70"}
5/11/21prodC30707550{"reqProdId":"prodC","reqValue":"30","prodA":"70","prodB":"75","prodC":"50"}{"reqProdId":"prodC","reqValue":"30","prodC":"50"}
5/12/21prodB10706550{"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:

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

ProdIdProdCategoryProdNameProdKeysProdProperties
S001ShirtsCrew Neck T-Shirt["type", "color", "fabric", "sizes"]["crew","blue","cotton","S,M,L","in stock","padded"]
S002ShirtsV-Neck T-Shirt["type", "color", "fabric", "sizes"]["v-neck","white","blend","S,M,L,XL","in stock","discount - seasonal"]
S003ShirtsTanktop["type", "color", "fabric", "sizes"]["tank","red","mesh","XS,S,M","discount - clearance","in stock"]
S004ShirtsTurtleneck["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:

ProdIdProdCategoryProdNameProdKeysProdPropertiesprodPropertyMap
S001ShirtsCrew 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" ] }
S002ShirtsV-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" ] }
S003ShirtsTanktop["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" ] }
S004ShirtsTurtleneck["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" ] }

See Also for EXAMPLE - ARRAYSTOMAP Function:

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:

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

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

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

See Also for EXAMPLE - Extract Keys From Objects:

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

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

See Also for EXAMPLE - Extract Object Values:

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:

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

VINProperties
XX3 JT4522year=2004,make=Subaru,model=Impreza,color=green,mileage=125422,cost=3199
HT4 UJ9122year=2006,make=VW,model=Passat,color=silver,mileage=102941,cost=4599
KC2 WZ9231year=2009,make=GMC,model=Yukon,color=black,mileage=68213,cost=12899
LL8 UH4921year=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:

VINyearmakemodelcolormileagecost
XX3 JT45222004SubaruImprezagreen1254223199
HT4 UJ91222006VWPassatsilver1029414599
KC2 WZ92312009GMCYukonblack6821312899
LL8 UH49212011BMW328ibrown5721216999

See Also for EXAMPLE - Extractkv and Unnest Transforms:

This page has no comments.