nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Lovisa

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

Accepted answers

jthi

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

All comments

stan_koprowski

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

example_json.json

how to parse a list of json objects to separate columns example.jmp

jthi

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?

Lovisa

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?

jthi

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;

bayesfactor1

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.

jthi

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