Working with Objects

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

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.

Transformation:

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

Results:

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:

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.

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.

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

Results:

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

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:

Source:

Your dataset contains master product data with product properties stored in two arrays of keys and values.

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:

You can now use the following steps to generate a new version of the keys:

Results:

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

Functions:

Source:

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

Code formatting has been applied to the Object data to improve legibility.

Transformation:

The following transformation extracts the keys from the Object data in the Configuration column.

Results:

The keys_Configuration column contains the arrays of the key values.

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:

Transformation:

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

Results:

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:

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.

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.

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:

Results:

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