how to use jsl to read json file time column

[#1]

I'm trying to use jsl to open a json file. The json file has a time column in the format of yyyy-mm-ddThh:mm:ss, but I'm struggling how to read it correctly. 

I tried to use this but it was not working

Open(path1,JSON Settings(
Stack( 0 ),
Row( "/root/value" ),
Col("/root/value/datetime",
Column Name( "datetime" ),
Fill( "Use Once" ),
Type( "Numeric" ),
Format( "yyyy-mm-ddThh:mm:ss" ),
Input format ( "yyyy-mm-ddThh:mm:ss" ),
Modeling Type( "Continuous" )
),

Any suggestions? Thank you!

[#2 · Accepted solution]

Most likely you want to load it as string and then change the format in JMP table (I don't think JSON specification has date format and usually they are stored as strings). Could you provide small example of the JSON file with at least the date key-value pair?

[#3]

Thank you for your response! It does default to read as Character. So maybe changing it in JMP is the way to go.

Here's an example of the json file,

{
"PrimaryKey": "14240",
"DateTimeStamp": "2024-06-25T16:17:34.524327-05:00",
"DateTimeStampLocal": "2024-06-25T16:17:34.523",
"DateTimeStampUtc": "2024-06-25T21:17:34.524327",
},

[#4]

Please use the 

     

icon located at the top of the Discussion input screen to copy in your JSL statements.  It really enhances the ability of the reading and understanding of the code.

[#5]

Will do!

[#6]

When you get the data to JMP table (Preview might work)

you can then use Word() (or Words()) and InFormat() (I suggest using Format Pattern with it) to clean your strings. If you need to keep the decimals it will be more complicated as you will have to handle those separately and same for the timezone.

Below is a small example which gives an idea what you can do

Names Default To Here(1);

str = "2024-06-25T16:17:34.524327-05:00";
{firstpart, lastpart} = Words(str, ".");

date = Informat(firstpart, "Format Pattern", "<YYYY>-<MM>-<DD>T<hh24>:<mm>:<ss>");
dec = Num("0." || Word(1, lastpart, "-+"));

[#7]

Not sure if I followed this. But I followed your first suggestion to read the json file in as string, and then change it to numeric time format later. And it worked! Thank you again for your help.

[#8]

JMP might be able to directly change the format of your date strings to JMP datenum and that happens when you convert the columns to numeric.

Using In Format() is for cases where you have to do something more complicated for the string formatted dates (sometimes required).