EXAMPLE - Extractkv and Unnest Transforms

This example shows how you can unpack data nested in an Object into separate columns using the following transforms:

• extractkv - Removes key-value pairs from a source string. See Extract Transform.
• unnest - Unpacks nested data in separate rows and columns. See Unnest Transform.

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.

Transform:

Add the following transform, 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.

extractkv col:Properties key:`{alpha}+` valueafter:`=` delimiter:`,`

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:

unnest col:extractkv_Properties keys:'year','make','model','color','mileage','cost'

Results:

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