EXAMPLE - Nest and Unnest JSON Records
This example illustrates how you can use unnesting and nesting transformations to reshape your JSON data.
The following example contains records of images from a website:
{"metrics": [{"rank": "1043", "score" : "9679"}], "caption": "Such a good boy!", "id": "9kt8ex", "url": "https://www.example.com/w285fpp11.jpg", "filename": "w285fpp11.jpg"} {"metrics": [{"rank": "1042", "score" : "9681"}], "caption": "This sweet puppy has transformed our life!", "id": "9x2774", "url": "https://www.example.com/fmll0cy11.jpg", "filename": "fmll0cy11.jpg"} {"metrics": [{"rank": "1041", "score" : "9683"}], "caption": "We sure love our fur babies.", "id": "a8guou", "url": "https://www.example.com/mljnmq521.jpg", "filename": "mljnmq521.jpg"}
Steps:
Add the JSON-based imported dataset to a flow and create a recipe for it.
Select the recipe, and click Edit Recipe....
In the Transformer page, the example above should look like the following:
metrics | caption | id | url | filename |
---|---|---|---|---|
[{"rank":"1043","score":"9679"}] | Such a good boy! | 9kt8ex | https://www.example.com/w285fpp11.jpg | w285fpp11.jpg |
[{"rank":"1042","score":"9681"}] | This sweet puppy has transformed our life! | 9x2774 | https://www.example.com/fmll0cy11.jpg | fmll0cy11.jpg |
[{"rank":"1041","score":"9683"}] | We sure love our fur babies. | a8guou | https://www.example.com/mljnmq521.jpg | mljnmq521.jpg |
Suppose you want to nest the url
and filename
columns into a nested array called, resources
.
Steps:
SHIFT + click the
url
andfilename
columns. Then, select Nest columns in the right-hand panel. This transformation should look like the following:Transformation Name
Nest columns into Objects
Parameter: column1
url
Parameter: column2
filename
Parameter: Nest columns to
Object
Parameter: New column name
column1
column1
now contains an Object mapping of the two columns. You can now nest this column again into an Array:Transformation Name
Nest columns into Objects
Parameter: Columns
column1
Parameter: Nest columns to
Array
Parameter: New column name
resources
Delete
column1
.Continue nesting other columns in a similar fashion. Repeat the above steps for the next level of the hierarchy in your dataset.