533809_en-US

liliconcom

How do you import JSONs that contain double nested arrays?

I'm trying to import a number of JSON files that contain double nested arrays (see example below). The arrays don't follow a consistent format, there could be numerous objects in each array or none at all. I want to have one score per row as shown below.

How can I get the data in this format using the import tool? Also, the name of the prediction is not always spelt the same e.g. sometimes it's "Small Cats" and sometimes it's "smallCats".

Data AccessRe: How do you import JSONs that contain double nested arrays?

This is a lot easier to answer if you can share an example JSON file that you would like to import.

Re: How do you import JSONs that contain double nested arrays?

I attached below, thanks!

Re: How do you import JSONs that contain double nested arrays?

You might have to write custom parser for this. Use Load Text File(file_path, JSON); and then parse the resulting Associative Array. It should be kinda simple if the format is consistent.

Re: How do you import JSONs that contain double nested arrays?

To get you going on the custom parses: you should have a look at the stack mole: https://community.jmp.com/t5/JSL-Cookbook/The-stack-mole/ta-p/302563

See attached implementation (requires your json file in the downloads folder). Not exactly what you want, but closer.

 

 

Re: How do you import JSONs that contain double nested arrays?

Here is an example you can try to get you started. It seems to do what you want, on the sample file you provided. In your particular case the custom parsing is relatively straightforward, although this isn't always the case. For deeper, more convoluted structures, parsing with recursion would likely be preferable.

 

Cheers,

Brady

 

Names Default To Here(1);

aa = Load Text File(pickfile(), "JSON");

/* since only the "prediction" key of the main array produces varying row values, the following AA can be used 
 to write those columns with nonvarying values, after all else is done.
*/
constantsAA = remove(aa, "prediction");


dt = new table("Parsed results",
 << new column("Points 1"), <<NEW column=""> 0, //if the value for this cat type is an empty list, there is no data, so skip the body of the loop
  for each({w, k}, aa["prediction"][v], //else, cycle through the list values, writing to the table
   dt << add rows(1);
   dt[nrow(dt), 1::4]  = shape( Matrix(w["data"]["points"] ), 1);  // places the values from {{x, y}, {w, z}}, into the 1st 4 table columns
   dt:Cat type = v;
   dt:Score = w["score"];
   dt: Frame Type = w["type"];
   
  );
 )
);

//now, cycle through the AA containing "constant" data, adding this to the table
//Note: the char() function was employed to deal with the "flagged" column's unquoted 'true' value.
for each({v, i}, constantsAA << get keys,
 dt << new column(v, character, <<SET each="" value=""></SET></NEW>

Re: How do you import JSONs that contain double nested arrays?

I had very similar version to @brady_brady's solution, but there are some small differences which might give some ideas to think about, so I'm sharing it:

View more...