EXAMPLE - Extract Object 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:
{
"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:
Nota
Change the column type to Object before applying the following transformation.
Nota
Each key must be entered on a separate line in the Path to elements area.
Transformation Name | |
|---|---|
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 |