JSON <=> JMP

[#1]

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!

[#2]

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,

TS

[#3]

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

[#4 · Accepted solution]

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

[#5 · Accepted solution]

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

      }

}

[#6]

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 )

[#7]

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.