Preserving column order when converting JSON to Data Table?

andykpdf

Hi All,

 

I have an add-in that uses some JSL to retrieve data from an API endpoint and turn it into a data table in JMP. Everything works fine, except that the resulting data table orders the JSON keys alphabetically.

 

I know that JSON keys are unordered, but is there any way to preserve their original sequential order when they are converted to data table columns? 

 

Thanks,

 

Andy 

request = New HTTP Request(
	URL( "https://xyz.com/api" ),
	Method( "Get" ),
	Query String(
		[["token" => "abcdefg"]]
	)
);
 
json = request << Send();
jsl_json = Parse JSON( json );
dt = JSON To Data Table( As JSON Expr( jsl_json["items"], Guess("wide") ) );

Craige_Hales

The re-ordering happens when you parse the json string into an associative array. You then extract a portion under the key "items", convert it back to another json string, and convert that to a data table. 

I'd suggest skipping most of that and using the json import wizard via open() rather than JsonToDataTable(). To get the script you need:

 

• savetextfile("$temp/x.json",json) and open it with the json wizard (choose the json filter on the file->open dialog). You might get lucky and be able to choose one of the guess values. If not, choose huge, turn on the stack check box, un-check the cols you don't want, un-check everything under ROWS except for "items". That will probably be pretty close to what you need.
• open and copy the source script from the resulting table. You can rearrange the COLS in the source script as needed. Use that script next time. The initial ordering of COLS is probably what you are asking for.

Here's an example that avoids the temp file by using a blob.

request = New HTTP Request( URL( "http://api.open-notify.org/iss-now.json" ), Method( "Get" ), Query String( [["token" => "abcdefg"]] ) );

jsontext = request << send; // not scrambled yet
jsonblob = Char To Blob( jsontext );
Open(jsonblob,
	JSON Settings(
		Stack( 0 ),
		Row( "/root" ),
		Col( "/root/timestamp", Column Name( "timestamp" ), Fill( "Use Once" ), Type( "Numeric" ), Format( {"Best"} ), Modeling Type( "Continuous" ) ),
		Col( "/root/iss_position/longitude", Column Name( "longitude" ), Fill( "Use Once" ), Type( "Numeric" ), Format( {"Best"} ), Modeling Type( "Continuous" ) ),
		Col( "/root/iss_position/latitude", Column Name( "latitude" ), Fill( "Use Once" ), Type( "Numeric" ), Format( {"Best"} ), Modeling Type( "Continuous" ) ),
		Col( "/root/message", Column Name( "message" ), Fill( "Use Once" ), Type( "Character" ), Format( {"Best"} ), Modeling Type( "Continuous" ) )
	),
	jsonwizard( 0 )
);