Creating a hierarchical map from a flat structure
This feature allows you to create a hierarchical structure based on an existing flat
structure and automatically map them together.
Before you begin
About this task
In this example, the input is a CSV file containing information about actors and actresses who have won Oscars between 1990 and 2016. The goal of the transformation is to get a JSON file in which the data is grouped by year, then by movie. The record for each movie will contain an array of the actors who won an Oscar for that movie.
You can create the input CSV file from the following
data:
Index,Gender,Year,Age,Name,Movie
1,female,1990,80,Jessica Tandy,Driving Miss Daisy
2,female,1991,42,Kathy Bates,Misery
3,female,1992,29,Jodie Foster,The Silence of the Lambs
4,female,1993,33,Emma Thompson,Howards End
5,female,1994,36,Holly Hunter,The Piano
6,female,1995,45,Jessica Lange,Blue Sky
7,female,1996,49,Susan Sarandon,Dead Man Walking
8,female,1997,39,Frances McDormand,Fargo
9,female,1998,34,Helen Hunt,As Good as It Gets
10,female,1999,26,Gwyneth Paltrow,Shakespeare in Love
11,female,2000,25,Hilary Swank,Boys Don't Cry
12,female,2001,33,Julia Roberts,Erin Brockovich
13,female,2002,35,Halle Berry,Monster's Ball
14,female,2003,35,Nicole Kidman,The Hours
15,female,2004,28,Charlize Theron,Monster
16,female,2005,30,Hilary Swank,Million Dollar Baby
17,female,2006,29,Reese Witherspoon,Walk the Line
18,female,2007,61,Helen Mirren,The Queen
19,female,2008,32,Marion Cotillard,La Vie en rose
20,female,2009,33,Kate Winslet,The Reader
21,female,2010,45,Sandra Bullock,The Blind Side
22,female,2011,29,Natalie Portman,Black Swan
23,female,2012,62,Meryl Streep,The Iron Lady
24,female,2013,22,Jennifer Lawrence,Silver Linings Playbook
25,female,2014,44,Cate Blanchett,Blue Jasmine
26,female,2015,54,Julianne Moore,Still Alice
27,female,2016,26,Brie Larson,Room
28,male,1990,32,Daniel Day-Lewis,My Left Foot
29,male,1991,42,Jeremy Irons,Reversal of Fortune
30,male,1992,54,Anthony Hopkins,The Silence of the Lambs
31,male,1993,52,Al Pacino,Scent of a Woman
32,male,1994,37,Tom Hanks,Philadelphia
33,male,1995,38,Tom Hanks,Forrest Gump
34,male,1996,32,Nicolas Cage,Leaving Las Vegas
35,male,1997,45,Geoffrey Rush,Shine
36,male,1998,60,Jack Nicholson,As Good as It Gets
37,male,1999,46,Roberto Benigni,Life Is Beautiful
38,male,2000,40,Kevin Spacey,American Beauty
39,male,2001,36,Russell Crowe,Gladiator
40,male,2002,47,Denzel Washington,Training Day
41,male,2003,29,Adrien Brody,The Pianist
42,male,2004,43,Sean Penn,Mystic River
43,male,2005,37,Jamie Foxx,Ray
44,male,2006,38,Philip Seymour Hoffman,Capote
45,male,2007,45,Forest Whitaker,The Last King of Scotland
46,male,2008,50,Daniel Day-Lewis,There Will Be Blood
47,male,2009,48,Sean Penn,Milk
48,male,2010,60,Jeff Bridges,Crazy Heart
49,male,2011,50,Colin Firth,The King's Speech
50,male,2012,39,Jean Dujardin,The Artist
51,male,2013,55,Daniel Day-Lewis,Lincoln
52,male,2014,44,Matthew McConaughey,Dallas Buyers Club
53,male,2015,33,Eddie Redmayne,The Theory of Everything
54,male,2016,41,Leonardo DiCaprio,The RevenantProcedure
-
Edit the output structure to create the hierarchy.
For more details about the features available, see Hierarchical output structure editor.The structure should look like this:
Results
The map is generated; you can click the output elements to see the expressions generated.
You can click Test Run to get a preview of the output. In this
example, the output looks like this:
{
"Oscars": [
{
"Year": "1990",
"Movies": [
{
"Movie": "Driving Miss Daisy",
"Actors": [
{
"Actor": {
"Name": "Jessica Tandy",
"Gender": "female",
"Age": "80"
}
}
]
},
{
"Movie": "My Left Foot",
"Actors": [
{
"Actor": {
"Name": "Daniel Day-Lewis",
"Gender": "male",
"Age": "32"
}
}
]
}
]
},
{
"Year": "1991",
"Movies": [
{
"Movie": "Misery",
"Actors": [
{
"Actor": {
"Name": "Kathy Bates",
"Gender": "female",
"Age": "42"
}
}
]
},
{
"Movie": "Reversal of Fortune",
"Actors": [
{
"Actor": {
"Name": "Jeremy Irons",
"Gender": "male",
"Age": "42"
}
}
]
}
]
},
{
"Year": "1992",
"Movies": [
{
"Movie": "The Silence of the Lambs",
"Actors": [
{
"Actor": {
"Name": "Jodie Foster",
"Gender": "female",
"Age": "29"
}
},
{
"Actor": {
"Name": "Anthony Hopkins",
"Gender": "male",
"Age": "54"
}
}
]
}
]
},
{
"Year": "1993",
"Movies": [
{
"Movie": "Howards End",
"Actors": [
{
"Actor": {
"Name": "Emma Thompson",
"Gender": "female",
"Age": "33"
}
}
]
},
{
"Movie": "Scent of a Woman",
"Actors": [
{
"Actor": {
"Name": "Al Pacino",
"Gender": "male",
"Age": "52"
}
}
]
}
]
},
{
"Year": "1994",
"Movies": [
{
"Movie": "Philadelphia",
"Actors": [
{
"Actor": {
"Name": "Tom Hanks",
"Gender": "male",
"Age": "37"
}
}
]
},
{
"Movie": "The Piano",
"Actors": [
{
"Actor": {
"Name": "Holly Hunter",
"Gender": "female",
"Age": "36"
}
}
]
}
]
},
{
"Year": "1995",
"Movies": [
{
"Movie": "Blue Sky",
"Actors": [
{
"Actor": {
"Name": "Jessica Lange",
"Gender": "female",
"Age": "45"
}
}
]
},
{
"Movie": "Forrest Gump",
"Actors": [
{
"Actor": {
"Name": "Tom Hanks",
"Gender": "male",
"Age": "38"
}
}
]
}
]
},
{
"Year": "1996",
"Movies": [
{
"Movie": "Dead Man Walking",
"Actors": [
{
"Actor": {
"Name": "Susan Sarandon",
"Gender": "female",
"Age": "49"
}
}
]
},
{
"Movie": "Leaving Las Vegas",
"Actors": [
{
"Actor": {
"Name": "Nicolas Cage",
"Gender": "male",
"Age": "32"
}
}
]
}
]
},
{
"Year": "1997",
"Movies": [
{
"Movie": "Fargo",
"Actors": [
{
"Actor": {
"Name": "Frances McDormand",
"Gender": "female",
"Age": "39"
}
}
]
},
{
"Movie": "Shine",
"Actors": [
{
"Actor": {
"Name": "Geoffrey Rush",
"Gender": "male",
"Age": "45"
}
}
]
}
]
},
{
"Year": "1998",
"Movies": [
{
"Movie": "As Good as It Gets",
"Actors": [
{
"Actor": {
"Name": "Helen Hunt",
"Gender": "female",
"Age": "34"
}
},
{
"Actor": {
"Name": "Jack Nicholson",
"Gender": "male",
"Age": "60"
}
}
]
}
]
},
{
"Year": "1999",
"Movies": [
{
"Movie": "Life Is Beautiful",
"Actors": [
{
"Actor": {
"Name": "Roberto Benigni",
"Gender": "male",
"Age": "46"
}
}
]
},
{
"Movie": "Shakespeare in Love",
"Actors": [
{
"Actor": {
"Name": "Gwyneth Paltrow",
"Gender": "female",
"Age": "26"
}
}
]
}
]
},
{
"Year": "2000",
"Movies": [
{
"Movie": "American Beauty",
"Actors": [
{
"Actor": {
"Name": "Kevin Spacey",
"Gender": "male",
"Age": "40"
}
}
]
},
{
"Movie": "Boys Don't Cry",
"Actors": [
{
"Actor": {
"Name": "Hilary Swank",
"Gender": "female",
"Age": "25"
}
}
]
}
]
},
{
"Year": "2001",
"Movies": [
{
"Movie": "Erin Brockovich",
"Actors": [
{
"Actor": {
"Name": "Julia Roberts",
"Gender": "female",
"Age": "33"
}
}
]
},
{
"Movie": "Gladiator",
"Actors": [
{
"Actor": {
"Name": "Russell Crowe",
"Gender": "male",
"Age": "36"
}
}
]
}
]
},
{
"Year": "2002",
"Movies": [
{
"Movie": "Monster's Ball",
"Actors": [
{
"Actor": {
"Name": "Halle Berry",
"Gender": "female",
"Age": "35"
}
}
]
},
{
"Movie": "Training Day",
"Actors": [
{
"Actor": {
"Name": "Denzel Washington",
"Gender": "male",
"Age": "47"
}
}
]
}
]
},
{
"Year": "2003",
"Movies": [
{
"Movie": "The Hours",
"Actors": [
{
"Actor": {
"Name": "Nicole Kidman",
"Gender": "female",
"Age": "35"
}
}
]
},
{
"Movie": "The Pianist",
"Actors": [
{
"Actor": {
"Name": "Adrien Brody",
"Gender": "male",
"Age": "29"
}
}
]
}
]
},
{
"Year": "2004",
"Movies": [
{
"Movie": "Monster",
"Actors": [
{
"Actor": {
"Name": "Charlize Theron",
"Gender": "female",
"Age": "28"
}
}
]
},
{
"Movie": "Mystic River",
"Actors": [
{
"Actor": {
"Name": "Sean Penn",
"Gender": "male",
"Age": "43"
}
}
]
}
]
},
{
"Year": "2005",
"Movies": [
{
"Movie": "Million Dollar Baby",
"Actors": [
{
"Actor": {
"Name": "Hilary Swank",
"Gender": "female",
"Age": "30"
}
}
]
},
{
"Movie": "Ray",
"Actors": [
{
"Actor": {
"Name": "Jamie Foxx",
"Gender": "male",
"Age": "37"
}
}
]
}
]
},
{
"Year": "2006",
"Movies": [
{
"Movie": "Capote",
"Actors": [
{
"Actor": {
"Name": "Philip Seymour Hoffman",
"Gender": "male",
"Age": "38"
}
}
]
},
{
"Movie": "Walk the Line",
"Actors": [
{
"Actor": {
"Name": "Reese Witherspoon",
"Gender": "female",
"Age": "29"
}
}
]
}
]
},
{
"Year": "2007",
"Movies": [
{
"Movie": "The Queen",
"Actors": [
{
"Actor": {
"Name": "Helen Mirren",
"Gender": "female",
"Age": "61"
}
}
]
},
{
"Movie": "The Last King of Scotland",
"Actors": [
{
"Actor": {
"Name": "Forest Whitaker",
"Gender": "male",
"Age": "45"
}
}
]
}
]
},
{
"Year": "2008",
"Movies": [
{
"Movie": "La Vie en rose",
"Actors": [
{
"Actor": {
"Name": "Marion Cotillard",
"Gender": "female",
"Age": "32"
}
}
]
},
{
"Movie": "There Will Be Blood",
"Actors": [
{
"Actor": {
"Name": "Daniel Day-Lewis",
"Gender": "male",
"Age": "50"
}
}
]
}
]
},
{
"Year": "2009",
"Movies": [
{
"Movie": "Milk",
"Actors": [
{
"Actor": {
"Name": "Sean Penn",
"Gender": "male",
"Age": "48"
}
}
]
},
{
"Movie": "The Reader",
"Actors": [
{
"Actor": {
"Name": "Kate Winslet",
"Gender": "female",
"Age": "33"
}
}
]
}
]
},
{
"Year": "2010",
"Movies": [
{
"Movie": "Crazy Heart",
"Actors": [
{
"Actor": {
"Name": "Jeff Bridges",
"Gender": "male",
"Age": "60"
}
}
]
},
{
"Movie": "The Blind Side",
"Actors": [
{
"Actor": {
"Name": "Sandra Bullock",
"Gender": "female",
"Age": "45"
}
}
]
}
]
},
{
"Year": "2011",
"Movies": [
{
"Movie": "Black Swan",
"Actors": [
{
"Actor": {
"Name": "Natalie Portman",
"Gender": "female",
"Age": "29"
}
}
]
},
{
"Movie": "The King's Speech",
"Actors": [
{
"Actor": {
"Name": "Colin Firth",
"Gender": "male",
"Age": "50"
}
}
]
}
]
},
{
"Year": "2012",
"Movies": [
{
"Movie": "The Artist",
"Actors": [
{
"Actor": {
"Name": "Jean Dujardin",
"Gender": "male",
"Age": "39"
}
}
]
},
{
"Movie": "The Iron Lady",
"Actors": [
{
"Actor": {
"Name": "Meryl Streep",
"Gender": "female",
"Age": "62"
}
}
]
}
]
},
{
"Year": "2013",
"Movies": [
{
"Movie": "Lincoln",
"Actors": [
{
"Actor": {
"Name": "Daniel Day-Lewis",
"Gender": "male",
"Age": "55"
}
}
]
},
{
"Movie": "Silver Linings Playbook",
"Actors": [
{
"Actor": {
"Name": "Jennifer Lawrence",
"Gender": "female",
"Age": "22"
}
}
]
}
]
},
{
"Year": "2014",
"Movies": [
{
"Movie": "Blue Jasmine",
"Actors": [
{
"Actor": {
"Name": "Cate Blanchett",
"Gender": "female",
"Age": "44"
}
}
]
},
{
"Movie": "Dallas Buyers Club",
"Actors": [
{
"Actor": {
"Name": "Matthew McConaughey",
"Gender": "male",
"Age": "44"
}
}
]
}
]
},
{
"Year": "2015",
"Movies": [
{
"Movie": "Still Alice",
"Actors": [
{
"Actor": {
"Name": "Julianne Moore",
"Gender": "female",
"Age": "54"
}
}
]
},
{
"Movie": "The Theory of Everything",
"Actors": [
{
"Actor": {
"Name": "Eddie Redmayne",
"Gender": "male",
"Age": "33"
}
}
]
}
]
},
{
"Year": "2016",
"Movies": [
{
"Movie": "Room",
"Actors": [
{
"Actor": {
"Name": "Brie Larson",
"Gender": "female",
"Age": "26"
}
}
]
},
{
"Movie": "The Revenant",
"Actors": [
{
"Actor": {
"Name": "Leonardo DiCaprio",
"Gender": "male",
"Age": "41"
}
}
]
}
]
}
]
}