Skip to main content

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:

  1. Add the JSON-based imported dataset to a flow and create a recipe for it.

  2. 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:

  1. SHIFT + click the url and filename 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
  2. 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
  3. Delete column1.

  4. Continue nesting other columns in a similar fashion. Repeat the above steps for the next level of the hierarchy in your dataset.