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

How do you import JSONs that contain double nested arrays?

BagOfMiningLion — Sat, 10 Jun 2023 20:52:08 GMT

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".

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

pauldeen — Mon, 15 Aug 2022 14:50:04 GMT

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?

BagOfMiningLion — Tue, 16 Aug 2022 08:08:35 GMT

I attached below, thanks!

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

jthi — Tue, 16 Aug 2022 09:40:36 GMT

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?

pauldeen — Tue, 16 Aug 2022 14:16:28 GMT

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?

brady_brady — Tue, 16 Aug 2022 16:23:23 GMT

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("Points 2"), <<new column("Points 3"), <<new column("Points 4"),
	<< new column("Cat Type", character),  <<new column("Score"), <<new column("Frame Type", character));
	 

// get a list of the possible cat types.
catList = aa["prediction"] << get keys;

// for each, see if there is any data. If so, write it to the table
for each ({v,i}, catList, 
	if(nitems(aa["prediction"][v]) > 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(char(constantsAA[v])))
);

/*
There may remain some cleanup work to script: 
-- preferential reordering of columns
-- modification of data type for some of the constant-valued columns as required
-- possibly will have to recode "small cats" and "Small Cats" to be the same thing. Ideally this would be addressed before writing
	the original JSON file (i.e., at the time of collecting the data, however this was done)
*/

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

jthi — Tue, 16 Aug 2022 16:41:27 GMT

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:

Names Default To Here(1);

file_path = "$DOWNLOADS\Test.json";
aa_all = Load Text File(file_path, JSON);

// separate prediction to it's own AA as it is only we need to loop over, rest are constants
aa_pred = Remove From(aa_all, "prediction");

// from each prediction, we need to collect:
	// data.points (multiple rows), score, type
// Note - we don't add points columns yet, because we don't how many we will have.
dt_pred = New Table("preds", 
	Add Rows(0),
	New Column("name", Character, Continuous),
	New Column("score", Numeric, Continuous),
	New Column("type", Character, Nominal)
);

// calculate max points and create points columns
max_points = 0;
For Each({{pred_key, pred_data}}, aa_pred,
	For Each({aa_data}, pred_data,
		If(Contains(aa_data, "data"),
			If(Contains(aa_data["data"], "points"),
				points_list = aa_data["data"]["points"];
				m_points = Matrix(points_list);
				new_max_points = N Rows(m_points) * N Cols(m_points);
				If(new_max_points > max_points,
					max_points = new_max_points;
				);
			);
		);
	);
);

point_cols = {};
For(i = 1, i <= max_points, i++,
	Insert Into(point_cols, dt_pred << New Column("Points", Numeric, Continuous));
);
point_cols[1] << Set Name("Points 1");

For Each({{pred_key, pred_data}}, aa_pred,
	If(N Items(pred_data) == 0,
		new_row = {};
		new_row = Eval List({pred_key, ., ""});
		Insert Into(new_row, AsList(J(1, max_points, .)`));
		dt_pred << Add Rows(1);
		dt_pred[N Rows(dt_pred), 0] = new_row;
	,
		For Each({aa_data}, pred_data,
			new_row = Eval List({pred_key, aa_data["score"], aa_data["type"]});
			Insert Into(new_row, As List(Shape(Matrix(aa_data["data"]["points"]), max_points, 1)));
			dt_pred << Add Rows(1);
			dt_pred[N Rows(dt_pred), 0] = new_row;
		);
	);
);

For Each({{key, val}}, aa_all,
	dt_pred << New Column(key, Character, Nominal, << Set Each Value(char(aa_all[key])));
);