Export JMP Table as JSON

[#1]

Extending @Craige_Hales excellent answer in JSON <=> JMP, is there a way to convert a JMP table to a JSON file that will preserve the row order of the original table (for human readability)?  Using JMP 17.2.0.

[#2]

To which format should this JMP table be changed to

New Table("Untitled",
	Add Rows(5),
	Compress File When Saved(1),
	New Column("name",
		Character,
		"Nominal",
		Set Values({"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE"})
	),
	New Column("age",
		Numeric,
		"Ordinal",
		Format("Fixed Dec", 5, 0),
		Set Values([12, 12, 12, 12, 12])
	),
	New Column("sex",
		Character(1),
		"Nominal",
		Set Values({"F", "F", "F", "F", "F"})
	),
	New Column("height",
		Numeric,
		"Continuous",
		Format("Fixed Dec", 5, 0),
		Set Values([59, 61, 55, 66, 52])
	),
	New Column("weight",
		Numeric,
		"Continuous",
		Format("Fixed Dec", 5, 0),
		Set Values([95, 123, 74, 145, 64])
	)
)

[
 {
  "name" : "KATIE",
  "age" : 12,
  "sex" : "F",
  "height" : 59,
  "weight" : 95
 },
 {
  "name" : "LOUISE",
  "age" : 12,
  "sex" : "F",
  "height" : 61,
  "weight" : 123
 },
 {
  "name" : "JANE",
  "age" : 12,
  "sex" : "F",
  "height" : 55,
  "weight" : 74
 },
 {
  "name" : "JACLYN",
  "age" : 12,
  "sex" : "F",
  "height" : 66,
  "weight" : 145
 },
 {
  "name" : "LILLIE",
  "age" : 12,
  "sex" : "F",
  "height" : 52,
  "weight" : 64
 }
]

Isn't the row order is preserved based on the order of items in the list?

[#3]

Hi @jthi,

Thanks for the response.  I should have included more detail.  The key order is not preserved in associative arrays.  For example, the sex order is not preserved in the script below.

I suppose this result will require an entirely new solution.  I am curious if someone is able to figure it out.

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Sort( Replace Table, By( :sex ), Order( Descending ) );

result = [=> ];

For( i = 1, i <= N Rows( dt ), i += 1,
	If( !Contains( result, dt:sex[i] ),
		result[dt:sex[i]] = [=> ]
	);
	If( !Contains( result[dt:sex[i]], dt:age[i] ),
		result[dt:sex[i]][dt:age[i]] = [=> ]
	);
	If( !Contains( result[dt:sex[i]][dt:age[i]], dt:name[i] ),
		result[dt:sex[i]][dt:age[i]][dt:name[i]] = [=> ]
	);
	result[dt:sex[i]][dt:age[i]][dt:name[i]]["height"] = dt:height[i];
	result[dt:sex[i]][dt:age[i]][dt:name[i]]["weight"] = dt:weight[i];
);

Write( As JSON Expr( result ) );

[#4]

I'm still not sure about the format you are looking for, I can see what the script creates (if I remember correctly that isn't valid JSON as the keys are not strings) but you want to basically group them based on something (in this case sex -> age -> name which are keys and then name has a value with more keys). What are you trying to do in the end?

[#5]

Output should be like below, except order of "F" and "M" should be reversed.

{
    "F": {
        12: {
            "JACLYN": {
                "height": 66,
                "weight": 145
            },
            "JANE": {
                "height": 55,
                "weight": 74
            },
            "KATIE": {
                "height": 59,
                "weight": 95
            },
            "LILLIE": {
                "height": 52,
                "weight": 64
            },
            "LOUISE": {
                "height": 61,
                "weight": 123
            }
        },
        13: {
            "ALICE": {
                "height": 61,
                "weight": 107
            },
            "BARBARA": {
                "height": 60,
                "weight": 112
            },
            "SUSAN": {
                "height": 56,
                "weight": 67
            }
        },
        14: {
            "CAROL": {
                "height": 63,
                "weight": 84
            },
            "ELIZABETH": {
                "height": 62,
                "weight": 91
            },
            "JUDY": {
                "height": 61,
                "weight": 81
            },
            "LESLIE": {
                "height": 65,
                "weight": 142
            },
            "PATTY": {
                "height": 62,
                "weight": 85
            }
        },
        15: {
            "AMY": {
                "height": 64,
                "weight": 112
            },
            "MARY": {
                "height": 62,
                "weight": 92
            }
        },
        16: {
            "MARION": {
                "height": 60,
                "weight": 115
            },
            "MARTHA": {
                "height": 65,
                "weight": 112
            }
        },
        17: {
            "LINDA": {
                "height": 62,
                "weight": 116
            }
        }
    },
    "M": {
        12: {
            "JAMES": {
                "height": 61,
                "weight": 128
            },
            "ROBERT": {
                "height": 51,
                "weight": 79
            },
            "TIM": {
                "height": 60,
                "weight": 84
            }
        },
        13: {
            "DAVID": {
                "height": 59,
                "weight": 79
            },
            "JOE": {
                "height": 63,
                "weight": 105
            },
            "JOHN": {
                "height": 65,
                "weight": 98
            },
            "MICHAEL": {
                "height": 58,
                "weight": 95
            }
        },
        14: {
            "ALFRED": {
                "height": 64,
                "weight": 99
            },
            "CHRIS": {
                "height": 64,
                "weight": 99
            },
            "EDWARD": {
                "height": 68,
                "weight": 112
            },
            "FREDERICK": {
                "height": 63,
                "weight": 93
            },
            "HENRY": {
                "height": 65,
                "weight": 119
            },
            "JEFFREY": {
                "height": 69,
                "weight": 113
            },
            "LEWIS": {
                "height": 64,
                "weight": 92
            }
        },
        15: {
            "CLAY": {
                "height": 66,
                "weight": 105
            },
            "DANNY": {
                "height": 66,
                "weight": 106
            },
            "MARK": {
                "height": 62,
                "weight": 104
            },
            "ROBERT": {
                "height": 67,
                "weight": 128
            },
            "WILLIAM": {
                "height": 65,
                "weight": 111
            }
        },
        16: {
            "PHILLIP": {
                "height": 68,
                "weight": 128
            }
        },
        17: {
            "KIRK": {
                "height": 68,
                "weight": 134
            },
            "LAWRENCE": {
                "height": 70,
                "weight": 172
            }
        }
    }
}

[#6 · Accepted solution]

Names have also changed order in your example. Only the most outer layer has to keep specific order?

I think any of the existing methods of directly creating JSON from JMP do not support format like that and as JMP doesn't have anything like OrderedDict from Python, the keys will be always sorted. If you have JMP18 you could maybe utilize Python integration. Purely in JMP/JSL I think you would have rely on some parts to string building. Other option in JSL is that you could most likely add extra information to your data to force the order, build associative array, build JSON string from that (don't save yet) and then use Substitute to replace those values back to the original ones.

Edit: You could also create the JSON string and then perform swaps there. Here is very quick example using substitute

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
dt << Sort(Replace Table, By(:sex), Order(Descending));

replacements = ["F" => "$002$", "M" => "$001$"];
:sex << Set Each Value(replacements[:sex]);

result = [=> ];

For(i = 1, i <= N Rows(dt), i += 1,
	If(!Contains(result, dt:sex[i]),
		result[dt:sex[i]] = [=> ]
	);
	If(!Contains(result[dt:sex[i]], dt:age[i]),
		result[dt:sex[i]][dt:age[i]] = [=> ]
	);
	If(!Contains(result[dt:sex[i]][dt:age[i]], dt:name[i]),
		result[dt:sex[i]][dt:age[i]][dt:name[i]] = [=> ]
	);
	result[dt:sex[i]][dt:age[i]][dt:name[i]]["height"] = dt:height[i];
	result[dt:sex[i]][dt:age[i]][dt:name[i]]["weight"] = dt:weight[i];
);

json_str = Substitute(As JSON Expr(result), replacements << get values, replacements << get keys);

Write(json_str);

This would require a quite bit of changes to make it more robust and dynamic (function to create replacement keys, using subset to avoid manipulating original data / utilizing column properties for this)

[#7]

Thanks.  That works great.

[#8]

I was working on a JMP to JSON script a while back.  Here is its current iteration.  As far as ordering the JSON file without alphabetical key order, (based on a value order property?), that would require another layer of complexity, writing the JSON expression out in incremental steps, I think, rather than relying on the "As JSON Expr()" function.  

Also, I have not yet written a JSON to JMP Script to convert this back to a data table.  The schema I used here is my own home-grown schema.  

Names Default To Here( 1 );
dt = Current Data Table();

aa = Associative Array();

aa["name"] = dt << Get Name( "Ignore Extension" );

// get table variables
tvarnames = dt << get table variable names;
If( N Items( tvarnames ),
	aa["table variables"] = Associative Array();
	For Each( {tvar, i}, tvarnames,
		Insert Into( aa["table variables"], tvar, dt << get table variable( tvar ) ) 
	
	);
);

// get table scripts
tscriptnames = dt << get table script names;
If( N Items( tscriptnames ),
	aa["table scripts"] = Associative Array();
	For Each( {tscript, i}, tscriptnames, 
	    // tscript = "DOE Dialog"
		Insert Into( aa["table scripts"], tscript, Char( dt << get script( tscript ) ) )
	);
);

// get script group names
script_groups = dt << get script groups names;

// iterate through each script group and get a list of the script in the group, add that to the associative array as a character string
If( N Items( script_groups ),
	aa["script groups"] = Associative Array();
	For Each( {scrgroup, i}, script_groups, 
	    //scrgroup="FIT Y BY X"
		Insert Into( aa["script groups"], scrgroup, Char( dt << get script group( scrgroup ) ) )
	);
);

// capture row states
// captured as a string representation, because as json expr() doesn't handle associative array elements that are lists or matrices very well
aa["row states"] = Char( dt << get row states );

// get labeled columns
// note that the jmp data table stores these as a table property and not as a property for each column
aa["label columns"] = Char( dt << get label columns );

// get columns, column properties and data
aa["columns"] = Associative Array();

For( i = 1, i <= N Cols( dt ), i++,
	col = Column( dt, i );
	colname = col << get name;
// create column entry
	aa["columns"][colname] = Associative Array();
// column order
	aa["columns"][colname]["column order"]=i;
// modeling type
	aa["columns"][colname]["modeling type"] = col << get modeling type;
// data type
	aa["columns"][colname]["data type"] = col << get data type;
// format
	aa["columns"][colname]["format"] = col << get format;
// get column properties
	colproperties = col << get properties list;
	If( N Items( colproperties ), 
		// note that << get properties list returns a list of expressions, converting them to character
		For( k = 1, k <= N Items( colproperties ), k++,
			colproperties[k] = Char( colproperties[k] )
		);
		//add properties element
		aa["columns"][colname]["properties"] = Associative Array();
		//add element for each property, capturing as character string to avoid expression eval issues
		For Each( {prop, k}, colproperties, aa["columns"][colname]["properties"][prop] = Char( col << get property( prop ) ) );
	);
// get data
	aa["columns"][colname]["data"] = As List( col << get values );
	
);
Show( aa );
nw = New Window( "Complete Data Table as JSON", sb = Text Box( char(As JSON Expr( aa )), <<set wrap(600))); 
sb << set wrap(300);

// uncomment and set path if you want to save to a text file
/*
path="<path>";

save text file(path || "jmpdt to json.json", as json expr(aa), mode("replace"));
*/