topic Re: How to load the JSON data into a data table with proper column name？ in Discussions

How to load the JSON data into a data table with proper column name？

sheephut — Sun, 11 Jun 2023 00:00:54 GMT

I want to know how to load a file with the following JSON data.

{
  "ErrorCode": 0,
  "ResultSets": [
    {
      "ColName": [
        "date",
        "name",
        "ZQDM",
        "sc",
        "content"
      ],
      "Content": [
        [
          "20230428",
          "*ST和科",
          "002816",
          0,
          "由和科达变为*ST和科"
        ],
        [
          "20230428",
          "*ST弘高",
          "002504",
          0,
          "由ST弘高变为*ST弘高"
        ],
        [
          "20230428",
          "*ST明诚",
          "600136",
          1,
          "由ST明诚变为*ST明诚"
        ],
        [
          "20230428",
          "*ST碳元",
          "603133",
          1,
          "由碳元科技变为*ST碳元"
        ],
        [
          "20230428",
          "*ST西域",
          "300859",
          0,
          "由西域旅游变为*ST西域"
        ],
        [
          "20230428",
          "ST天顺",
          "002800",
          0,
          "由天顺股份变为ST天顺"
        ],
        [
          "20230428",
          "ST宇顺",
          "002289",
          0,
          "由宇顺电子变为ST宇顺"
        ],
        [
          "20230428",
          "ST贵人",
          "603555",
          1,
          "由贵人鸟变为ST贵人"
        ],
        [
          "20230428",
          "ST迪威迅",
          "300167",
          0,
          "由迪威迅变为ST迪威迅"
        ]
      ]
    }
  ]
}

The effective data is the node of "/root/ResultSets/", it is composed by two parts, the first part is the list of the columns' name as the node "/root/ResultSets/ColName", the second part is the body of the data table as the node "/root/ResultSets/Content", depicted by a two-dimension JSON datum.

Now I used the following JSON Settings in open file function, but cannot set the columns'name with the data in node "/root/ResultSets/ColName".

	JSON Settings(
		Stack( 0 ),
		Col(
			"/root/ResultSets/Content",
			Column Name( "Content" ),
			Fill( "Use Once" ),
			Type( "Pandas Values" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		)
	),

I want to know how to load the JSON data into a data table with proper column name.

Re: How to load the JSON data into a data table with proper column name？

Craige_Hales — Tue, 02 May 2023 01:40:27 GMT

The JSL and video should help answer the question.

/*
There are two kinds of JSON: 
o  The good kind: the keys are the names of the data. A good kind example has 
   a key named "date" with date values for the data.
o  The bad kind: the keys are data values. A bad kind example has a huge 
   number of different keys with names 1jan2020, 2jan2020... JMP does not 
   have a great way to import the bad kind.

This example is a good kind example. It does not quite match one
of the standard pandas formats or the standard rows-with-headers format.
But it is not abusing keys to hold data, so it can be made to work.
 
It could be treated as two tables with some JSL to make a minor fixup.
o  a table of one colname per row, and
o  a table of rows of 5 items each.

Or it can be treated as one table with some JSL to make a minor fix up.
The fixup JSL here is for the one table case; the two table case needs
a simpler version to rename the columns.
*/

j =
"\[
	{
  "ErrorCode": 0,"ResultSets": [
    {
      "ColName": [ "date","name","ZQDM","sc","content" ],
      "Content": [
		["20230428","*ST和科","002816",0,"由和科达变为*ST和科"],
		["20230428","*ST弘高","002504",0,"由ST弘高变为*ST弘高"],
		["20230428","*ST明诚","600136",1,"由ST明诚变为*ST明诚"],
		["20230428","*ST碳元","603133",1,"由碳元科技变为*ST碳元"],
		["20230428","*ST西域","300859",0,"由西域旅游变为*ST西域"],
		["20230428","ST天顺","002800",0,"由天顺股份变为ST天顺"],
		["20230428","ST宇顺","002289",0,"由宇顺电子变为ST宇顺"],
		["20230428","ST贵人","603555",1,"由贵人鸟变为ST贵人"],
		["20230428","ST迪威迅","300167",0,"由迪威迅变为ST迪威迅"]
      ]
    }
  ]
}
]\";

// there are two ways: save text file, then open, OR convert to blob, then open

//open(chartoblob(j),jsonwizard(1));
// or...
file = Save Text File( "$temp/x.json", j );
Open( file, jsonwizard( 1 ) );

// at this point, watch the video. the json settings section is the copy/paste from the video.
// Or, you could let the wizard finish importing and grab the complete script from the table.
// either way, the fix up step below is still needed because the shape of the JSON is not quite
// right for the wizard to do a perfect job.

dt = JSON To Data Table(
	j, 
 // paste here
	JSON Settings(
		Stack( 1 ),
		Col(
			"/root/ResultSets/ColName",
			Column Name( "ColName" ),
			Fill( "Use Once" ),
			Type( "JSON Lines with Headers" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		),
		Col(
			"/root/ResultSets/Content",
			Column Name( "Content" ),
			Fill( "Use Once" ),
			Type( "Pandas Values" ),
			Format( {"Best"} ),
			Modeling Type( "Continuous" )
		)
	)
);

// fix up. the first 5 columns have the name, but no data
// the last 5 columns have the data but the wrong name.
nc = N Cols( dt ) / 2; // get number of cols. the first half are the names, the second half are the data.
For( i = 1, i <= nc, i += 1,
	cname = Column( dt, 1 ) << getname; // name from col 1
	dt << deletecolumn( 1 ); // col 1 goes away
	Column( dt, nc ) << setname( cname ); // col 5 gets the name (was col 6 before delete)
	Wait( 1 ); // just for the video
);

(view in My Videos)

Re: How to load the JSON data into a data table with proper column name？

sheephut — Tue, 02 May 2023 04:41:28 GMT

Thank you for so detailed illustration with video. The ingenious idea and clever code have implemented the functionality I need so powerfully.

Would you like to pay more effort to give me some help about "stack()"? I don't know the concept of "stack()" and the difference among the argument 0 or 1 or any other values?

Re: How to load the JSON data into a data table with proper column name？

Craige_Hales — Tue, 02 May 2023 11:30:07 GMT

Stack is a boolean checkbox in the GUI, 0 is unchecked, 1 is checked. Sometimes the data is organized in a way that it is better checked, or maybe better unchecked. With your data:

stack(0) the data goes across

stack(1) the data goes down

In this example, ResultSet is the key selected to generate rows. That means each time a ResultSet key is closed, row(s) will be generated. In this example there is only one ResultSet key, so there is only one time when it closes. ColName is the key chosen to make columns. The five values will all be in the same cell of a single row for stack(0). For stack(1), multiple rows are generated each time the ResultSet key is closed. If there are multiple stackable columns with stack(1), their values are emitted in parallel when the ResultSet key is closed.

If there are several columns with the same number of values, stack(1) is very useful. If there is only one column, and you do it like this, stack makes no difference:

Using ColName to create the rows and the column

The ColName now generates a row for each new value it finds.

The Fill parameter is also related to this. Add the ErrorCode:

ErrorCode added with 'use once'

ErrorCode with 'Use Forever'.

Re: How to load the JSON data into a data table with proper column name？

lwx228 — Wed, 03 May 2023 08:33:48 GMT

I'm still using JMP 14

As if JSL doesn't work like this, how can such structured data be quickly converted to tables using the convert to blob method?

Thank Craige!

Re: How to load the JSON data into a data table with proper column name？

Craige_Hales — Wed, 03 May 2023 11:12:21 GMT

The XML and JSON import wizard first appeared in JMP 15.

JMP 14 probably had some JSON import capability, but does not support the extended syntax the wizard uses.

Depending on the JSON file, JMP 14's support might be good enough.

JSON To DataTable will work for some strings well enough.

I think XML support is not in 14 except through the XML parsing functions.

JMP 14 can open from a blob, like this

open(chartoblob("a,b,c\!n1,2,3"),"text")

but only for the file types it understands. For some reason, "text" files mean CSV files...

Re: How to load the JSON data into a data table with proper column name？

UersK — Mon, 10 Jul 2023 14:06:53 GMT

If this JSON data is downloaded via POST, how can I convert this data from JSON to table data in memory without first saving it as a local file?

rs = New HTTP Request( URL( u ), Method( "POST" ), JSON( jj ), Headers( h ) );
rr = rs << Send;
//？？

dt=Open(chartoblob( rr ),"json",JSON Settings(Stack(0),Row("/root/data"),Col("/root/data",Fill("Use Once"),Type("Pandas Values") )),JSON Wizard(0));

Thank Craige!

Re: How to load the JSON data into a data table with proper column name？

Craige_Hales — Mon, 10 Jul 2023 18:25:49 GMT

jsonToDataTable(), if you like what it does.

Or use

open( chartoblob( text ), json wizard options )

To use the wizard, save an example table to disk, run the wizard, grab the script and modify the filename in the open to be chartoblob(json text).