topic Re: How to parse a list of JSON objects to separate columns? in Discussions

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

Lovisa — Sat, 10 Jun 2023 23:47:21 GMT

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

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

stan_koprowski — Tue, 19 Apr 2022 15:05:58 GMT

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

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

jthi — Tue, 19 Apr 2022 15:05:42 GMT

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?

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

Lovisa — Wed, 20 Apr 2022 10:15:10 GMT

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?

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

jthi — Wed, 20 Apr 2022 16:09:57 GMT

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;

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

bayesfactor1 — Mon, 21 Nov 2022 13:19:56 GMT

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.

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

jthi — Mon, 21 Nov 2022 13:38:26 GMT

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