thank you @Craige_Hales
I have a few issues with my json parsing actually. I get millions of them from a data base so i download them all into a jmp data table using sql. Then, parse them one by one to extract the information i need. i would like to have a row for each child, and all the information of the person duplicated per child.
consider the following script. this was produced manually using the wizard. since the names of the children are in the titles the wizard is not aggregating them by default. if i had the word "child" repeating instead of tish, mimi and berta it would guess it perfectly using the long guess.
how do i go from there to make this accommodate any number of different children the json may have? how do i change the hard coding of tish, mimi and berta? also, some children's attributes may be missing so i would need to be able to skip them.
JSON To Data Table(
"\[
{
"Person": "sami",
"Age": "45",
"event": "came home",
"when": 1639682643.7996492,
"Children": {
"tish": [
{
"Name": "tish",
"Father": "bob",
"Age": "14",
"event": "went to school",
"when": 1639682630.1256561
}
],
"mimi": [
{
"Name": "mimi",
"Father": "bob",
"Age": "25",
"event": "went to play",
"when": 1639682630.1256561
}
],
"berta": [
{
"Name": "berta",
"Father": "bob",
"Age": "23",
"event": "went to work",
"when": 1639682630.1256561
}
]
}
}
]\",
JSON Settings(
Stack( 0 ),
Row( "/root/Children/tish" ),
Row( "/root/Children/mimi" ),
Row( "/root/Children/berta" ),
Col(
"/root/Person",
Column Name( "Person" ),
Fill( "Use Forever" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Age",
Column Name( "person Age" ),
Fill( "Use Forever" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/event",
Column Name( "person event" ),
Fill( "Use Forever" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/when",
Column Name( "person when" ),
Fill( "Use Forever" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children",
Column Name( "Children" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/tish/Name",
Column Name( "Name" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/tish/Father",
Column Name( "Father" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/tish/Age",
Column Name( "Age" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/tish/event",
Column Name( "event" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/tish/when",
Column Name( "when" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/mimi/Name",
Column Name( "Name" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/mimi/Father",
Column Name( "Father" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/mimi/Age",
Column Name( "Age" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/mimi/event",
Column Name( "event" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/mimi/when",
Column Name( "when" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/berta/Name",
Column Name( "Name" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/berta/Father",
Column Name( "Father" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/berta/Age",
Column Name( "Age" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/berta/event",
Column Name( "event" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
),
Col(
"/root/Children/berta/when",
Column Name( "when" ),
Fill( "Use Once" ),
Type( "Character" ),
Format( {"Best", 15} ),
Modeling Type( "Continuous" )
)
),
JSON Wizard( 1 )
);
so far i was looping through the keys at every level (brut-force) but it took over three days to go over a sample of 6 million jsons.