How to parse a list of JSON objects to separate columns?

[#1]

I'm trying to import the following data to JMP but all external data objects ends up in the same column. Is there a way to get it in separate columns? I get it to work with numerical values but not with strings. All strings ends up in the same columns with comma separated values. Thankful for your help.

[{
"id": 1234,
"date": "2022-04-06",
"batch": {
"id": 1553,
"date": "2022-02-01"
},
"externalData": [
{
"orderNumber": 1,
"batchNumber": "bo09",
"partNumber": 121
},
{
"orderNumber": 1,
"batchNumber": "hoi112",
"partNumber": 129
},
{
"orderNumber": 1,
"batchNumber": "1ka10",
"partNumber": 432
} ,
{
"orderNumber": 1,
"batchNumber": "11la0",
"partNumber": 432
}
]
},{
"id": 1234,
"date": "2022-04-06",
"batch": {
"id": "1553",
"date": "2022-02-01"
},
"externalData": [
{
"orderNumber": 1,
"batchNumber": "14",
"partNumber": 121
},
{
"orderNumber": 1,
"batchNumber": "112",
"partNumber": 129
},
{
"orderNumber": 1,
"batchNumber": "110",
"partNumber": 432
}
]
}]

[#2]

Hi @Lovisa,

Depending on which version of JMP you are using you can import the JSON file directly into JMP using the File Open command.

 

Import JSON File-->Open

Import JSON Red Triangle Option Tall Format

Select the "Tall guess" option from the red triangle menu option.

And after clicking OK you get the JMP data table with a source script that you can reuse as needed for importing additional files.

Open(
	"$Desktop/example_json.json",
	JSON Settings(
		Stack( 0 ),
		Row( "/root/externalData" ),
		Col(
			"/root/externalData/orderNumber",
			Column Name( "orderNumber" ),
			Fill( "Use Once" ),
			Type( "Numeric" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		),
		Col(
			"/root/externalData/batchNumber",
			Column Name( "batchNumber" ),
			Fill( "Use Once" ),
			Type( "Character" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		),
		Col(
			"/root/externalData/partNumber",
			Column Name( "partNumber" ),
			Fill( "Use Once" ),
			Type( "Numeric" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		)
	),
	JSON Wizard( 0 )
)

cheers,

Stan

[#3]

Thanks for your reply! I've tried the different options in the import wizard but I would like to separate the list into different columns not rows as in your solution. Is there a way to do that?

[#4]

Depends on how you want the data to look like, it might be that JSON Wizard cannot format it like you want to.

JSON Import Wizard with Huge Guess (Wide will most likely also work):

Then you can separate batchNumber to different columns:

Or using a bit more complicated JSON Import Wizard settings:

And from here you can use different tools JMP offers to split/stack/transpose the data.

You could also leave batch.id with empty values and use that to calculate list index

If(Row() == 1, val = 0);
If(Is Missing(:batch.id),
	val,
	val
	++);
val;

[#5]

Have you tried the different options JSON Import Wizard provides you with? Here is for example Wide Guess with Stack enabled:

How do you want the data to look like?

[#6]

If my JSON data isn't a file but is a column in an existing datatable, how can I parse the JSON into separate columns? In the example screenshot below, I want to parse the "results" column out into 3 separate columns: result1, result2, result3.

[#7 · Accepted solution]

Writing separate formulas for each of the results is one option

Parse JSON(:Column 1[1])["res1"];

This can also be scripted to be more automatic