topic Re: JSON <=> JMP in Discussions

JSON <=> JMP

miguello — Sat, 10 Jun 2023 23:37:22 GMT

All,

I have a 2 part question.

Part 1.

Let's say I have a JSON file of this kind:

[
    {
        "Cat1":  {
			"SubCat1": {
				"Param1": "AA", 
				"Param2": "BB" 
			},
			"SubCat2": {
					"Param1": "AB", 
					"Param2": "BC" 
				}
		},
        "Cat2":  {
			"SubCat1": {
				"Param1": "BA", 
				"Param2": "BC" 
			},
			"SubCat2": {
					"Param1": "CB", 
					"Param2": "BD"
				}
		}
    }
]

How would I import it to JMP (using GUI or scripts) to get something like this:

So far I'm just getting bunch of columns and one row.

Part 2.

Now I need to convert JMP table to JSON file. Let's say I have a JMP file that looks like above. Simple saving it as JSON would lead to this:

[
 {
  "Category" : "Cat1",
  "SubCategory" : "SubCat1",
  "Parameter" : "Param1",
  "Value" : "AA"
 },
 {
  "Category" : "Cat1",
  "SubCategory" : "SubCat1",
  "Parameter" : "Param2",
  "Value" : "BB"
 },
 {
  "Category" : "Cat1",
  "SubCategory" : "SubCat2",
  "Parameter" : "Param1",
  "Value" : "AB"
 },
 {
  "Category" : "Cat1",
  "SubCategory" : "SubCat2",
  "Parameter" : "Param2",
  "Value" : "BC"
 },
 {
  "Category" : "Cat2",
  "SubCategory" : "SubCat1",
  "Parameter" : "Param1",
  "Value" : "BA"
 },
 {
  "Category" : "Cat2",
  "SubCategory" : "SubCat1",
  "Parameter" : "Param2",
  "Value" : "BC"
 },
 {
  "Category" : "Cat2",
  "SubCategory" : "SubCat2",
  "Parameter" : "Param1",
  "Value" : "CB"
 },
 {
  "Category" : "Cat2",
  "SubCategory" : "SubCat2",
  "Parameter" : "Param2",
  "Value" : "BD"
 }
]

Which is not good.

But if I construct an AA that looks like this and save it as JSON:

AA = [
	        "Cat1"=>  [
			"SubCat1"=> [
				"Param1"=> "AA", 
				"Param2"=> "BB" 
			],
			"SubCat2"=> [
					"Param1"=> "AB", 
					"Param2"=> "BC" 
				]
		],
        "Cat2"=>  [
			"SubCat1"=> [
				"Param1"=> "BA", 
				"Param2"=> "BC" 
			],
			"SubCat2"=> [
					"Param1"=> "CB", 
					"Param2"=> "BD"
				]
		]
    ];
    
JSON = As JSON Expr(AA);

Save Text File("C:\Users\MyUser\JMPtoJSON\AAtoJSON.json", JSON)

then I get JSON I need.

Question - what is the most efficient way of constructing AA like that from JMP table like that?

Thanks!

Re: JSON <=> JMP

Thierry_S — Wed, 22 Sep 2021 20:58:50 GMT

Hi,

At first glance, your original JSON file does not include any column header which my confuse JMP JSON import routine. Just a thought.

Best,

Re: JSON <=> JMP

miguello — Wed, 22 Sep 2021 21:21:46 GMT

I would guess it should use generic names like "Column1" then or if done through GUI just have option to input it.

I think it should have an option to say - everything from level 1 goes to this column, everything from level 2 goes to that column, and so on...

Re: JSON <=> JMP

Craige_Hales — Wed, 22 Sep 2021 22:28:35 GMT

part 1 answer

dt1 = Open(
	Char To Blob(
		"\[[
    {
        "Cat1":  {
			"SubCat1": {
				"Param1": "AA", 
				"Param2": "BB" 
			},
			"SubCat2": {
					"Param1": "AB", 
					"Param2": "BC" 
				}
		},
        "Cat2":  {
			"SubCat1": {
				"Param1": "BA", 
				"Param2": "BC" 
			},
			"SubCat2": {
					"Param1": "CB", 
					"Param2": "BD"
				}
		}
    }
]]\"
	),
	json
);

dt2 = dt1 << Transpose(	columns( dt1 << getcolumnnames ) ); // the 8 columns become the 8 rows
name = {"category", "SubCategory", "Parameter"}; // add these new column names
For( i = 1, i <= nitems(name), i += 1,
	nc = dt2 << New Column( name[i], formula( Word( i, label, "." ) ) );
	dt2 << runformulas; // run, 
	nc << deleteformula(); // and remove so label is no longer referenced
);
// pretty it up...
dt2:row1 << setname( "value" );
dt2 << deletecolumns( label ); // unreferenced now, and not needed
// move value to end if desired...
dt2:value << setselected; dt2 << move selected columns( to last ); dt2 << Clear Column Selection();

The import table has the data, in one row, but encoded into the column names. After transposing, use the word() function in a column formula to split the dotted name into 3 columns. Remove the formula so the label column can be removed since it is now redundant.

transposed and column-ized

Re: JSON <=> JMP

Craige_Hales — Wed, 22 Sep 2021 23:05:36 GMT

part 2, using dt2 from part 1. For each row, create the key+AA if not already created, then add the next level key. This will keep the last occurrence of a duplicated row.

result = [=> ];
For( i = 1, i <= N Rows( dt2 ), i += 1,
	If( !Contains( result, dt2:category[i] ),
		result[dt2:category[i]] = [=> ]
	);
	If( !Contains( result[dt2:category[i]], dt2:SubCategory[i] ),
		result[dt2:category[i]][dt2:SubCategory[i]] = [=> ]
	);
	result[dt2:category[i]][dt2:SubCategory[i]][dt2:Parameter[i]] = dt2:value[i];
);
Write( As JSON Expr( result ) );

{

"Cat1":

{

"SubCat1":{"Param1":"AA","Param2":"BB"},

"SubCat2":{"Param1":"AB","Param2":"BC"}

"Cat2":

{

"SubCat1":{"Param1":"BA","Param2":"BC"},

"SubCat2":{"Param1":"CB","Param2":"BD"}

}

Re: JSON <=> JMP

Craige_Hales — Wed, 22 Sep 2021 23:24:42 GMT

As @Thierry_S pointed out, the JSON format is using data rather than names for keys. Your example of JMP's preferred save-to-json uses the column names for the keys.

Pandas JSON formats can squeeze out some of the redundancy, and JMP can read them (but not directly write them. @Audrey_Shull )

Re: JSON <=> JMP

miguello — Wed, 22 Sep 2021 23:31:08 GMT

Thanks, Craig,

Good hint for me to use

!Contains()

I started creating AAs for each column and getting keys to get unique values for that particular level to iterate over them and made it too complicated. Your method is much simpler.