**************** New JSON Extender (Experimental) *******************

[td]

The new JSON extender is available for download.

The JSON extender project along with several other languishing projects was initiated quite some time ago but never completed. This is the first one to finally meet the light of day.

What is JSON and why create a JSON WinBatch extender? To borough from the extender's help file, "JSON stands for JavaScript Object Notation and is a lightweight, easy to understand format used to store and transport data. It is often used to send data to and from websites and Internet based applications but it is increasingly used by local applications to store configuration and other data on the local system. "

Why do we call this pre-release extender "experimental"? In the world of software development, experimental releases are different from beta releases because beta releases are considered feature complete. Experimental releases may or may not be feature complete. The differences between a beta release and a final release are usually confined to bug fixes and performance improvements. The difference between experimental releases and final releases may include new or modified features.

We created the extender using a new-to-us software development approach and some new software development tools so it is experimental in those senses too.

You can download the extender by following the link below. You will need a current license to install the extender.

https://files.winbatch.com/downloads/wb/ilcjs44i.zip

Any constructive comments and suggestions are appreciated.

[JTaylor]

Excellent.  Thank you.  One thing I would request that I have found helpful would be a way to list all the paths like I do in my Omnibus Extender.   That is VERY helpful for getting a feel for the data and paths.   Maybe if the key-name in jsKeyPaths() was left blank it would list out all the paths from that point down?   Option to list value at the end of appropriate paths would be icing on the cake.



Jim

P.S.  If anyone is using the JSON portion of my Extender I recommend you switch and let me know.  Once I am somewhat sure no one is using it or at least some time passes I will be removing JSON support so I don't have to keep up with that part of the Omnibus Extender.   Thanks again Tony.   Hope to see the SQLite Extender next :)

[td]

Actually, jsKeyPaths does return all paths if the key-name parameter is an empty string. Unfortunately, the fact just isn't mentioned in the help file. Will have to make sure that the situation is corrected.

Thanks for pointing it out.

[stanl]

The new JSON extender is available for download.

Nice. Hope to find time this weekend to test with some REST queries.

[stanl]

Nice. Hope to find time this weekend to test with some REST queries.

Well, didn't take long to get a failure... probably something I did. Used the same weather alerts URL I tested with Jim's extender and Newtonsoft Json.  Got an invalid argument on the jsValid() function.

Code (WINBATCH) Select Expand



;Winbatch 2021C - Test of new Json Extender
;uses  WinHttp.WinHttpRequest.5.1 for JSON return
;Stan Littlefield, September 10, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender('ilcjs44i.dll', 0, 'ilcjs64i.dll')
ar = "NJ" ;choose a different state to test outut
cUrl = "https://api.weather.gov/alerts/active?area=%ar%"
BoxOpen("Parsing:":cUrl,"Please Wait...")
cSerialize = Dirscript():ar:".txt"
If FileExist(cSerialize) Then FileDelete(cSerialize)       
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
jdata = request.ResponseText
request = 0


;just display Json text
Message("",jdata)
;=================================
Valid = jsValid(jdata)  ;fails with 'Invalid Argument'
;=================================
If Valid Then Result = 'String is valid JSON.'
Else Result = 'Sting is not valid JSON data'
Message(cURL,Result)


If Valid
   Object = jsParse(jdata)
   FormJson =  jsSerialize(Json ,@true, @JsonUTF8)
   jsObjClose()
   FilePut(cSerialize,FormJson)
Else
   jsObjClose()
Endif


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine




Return


[kdmoyers]

It works!  Thanks Tony!

[td]

Well, didn't take long to get a failure... probably something I did. Used the same weather alerts URL I tested with Jim's extender and Newtonsoft Json.  Got an invalid argument on the jsValid() function.

It didn't take long to fix the problem either. You are passing the function a variant bstr which the WIL interpreter converts to the extender required Unicode as well as preserving the variant version in the variable. However, the extender is strict about parameter types so it expects only Unicode. We will adjust the parameter checking and post an update later today.

[edit] You can work around the problem by striping the bstr from the string doing something like "jData = StrTrim(jData)" before passing the string to any functions. Also, your script has a coding bug. The "Json" parameter to the jsSerialize function is not defined.

[stanl]

Also, your script has a coding bug. The "Json" parameter to the jsSerialize function is not defined.

Noted: it was a quickie test. Changed Json to Object and modified: jdata = StrTrim(request.ResponseText) so test now works.

[stanl]

After the initial test worked, I modified this section to:

Code (WINBATCH) Select Expand



If Valid
   Object = jsParse(jdata)
   TypesMap = jsObjMap(Object, @JsonValue)
   jsObjClose()
   MapFilePutCSV(cSerialize,TypesMap, ",",@true,0)
Else
   jsObjClose()
Endif



but not a lot of actionable data is returned. My objective is to process Json returns from REST API queries and convert to Excel. This would entail a Json object to map to headers[keys] and values with multiple values per header which is precluded by WB Maps, or I assume maps in general, where keys are distinct. For example:


{
parent: {
               key1:value
               key2:value }
}

parent: {
               key1:value
               key2:value }
}


}


would have parent as the Excel Tab Name, then


key1       key2
value      value
value      value




I'm sure the Extender can address this with some foreach looping.... but wanted to bring up for others who might want to make sense of Json data.

[td]

The extender does not expand child objects in maps and you are correct that map keys must be unique. Objects are not expanded because maps cannot be nested. The purpose of returning maps is to provide quick a dirty access to simple JSON objects.  Child objects are represented as extender object handles in the WIL Map so you could create a sub-map for each child by access the handle in the parent map. You would have to write the script to deal with nesting depth up to 32 levels. The alternate solution would be to get a list of all the paths in the object and get the value of each.

We could provide an array or map as a user-selectable return value alternate for the jsKeyPaths function. This would improve script performance in cases where you are spinning through a long list of paths. 

[td]

I realize that is just an example to make a point but the  JSON object

{
parent: {
               key1:value
               key2:value }
}

parent: {
               key1:value
               key2:value }
}


}

does validate and parse but since you can't have two keys with the same name at the same level within a JSON object the second object overwrites the first. This expected behavior is shown by online JSON validators. In order to do what your example purposes would require placing the objects in a JSON array.

[td]

A new (already) version of the extender is in the download area the link is the same.

https://files.winbatch.com/downloads/wb/ilcjs44i.zip

This version addresses the parameter validation issue and adds some neglected verbiage in the help file.

The feedback has been tremendous.  Taking the time to look at the extender is much appreciated. Please feel free to make additional comments and suggestions.

[stanl]

In order to do what your example purposes would require placing the objects in a JSON array.

Understood. So assuming I was scraping consistent REST results and knew the data was a Json array with a 'parent' key, no problem. But [perhaps a holy grail] to determine that on inspection of the data - first time.

[td]

Not sure I understand what you are suggesting but you can use the jsValueType function to determine the JSON type of an element in a JSON tree of objects include JSON containers, i.e., objects and arrays. You can collect the types of all the elements at once using jsKeyPath to get all paths and jsValueType to get the type of each key in the path and the type of each element in any arrays in the object or objects.

[stanl]

Not sure I understand what you are suggesting but you can use the jsValueType function to determine the JSON type of an element in a JSON tree of objects include JSON containers, i.e., objects and arrays. You can collect the types of all the elements at once using jsKeyPath to get all paths and jsValueType to get the type of each key in the path and the type of each element in any arrays in the object or objects.

I missed the "" to get all Paths. A little spoiled with Jim's use of 'tree'..  ended up writing a udf crTree() which does exactly what I need. I'll end up referring to branches or limbs rather than paths/keys :o  but the extender is really slick!

[stanl]

Attached a sample Json text [just rename extension to .json for script].  Basically set up UDf and type map so the script creates a documenter of sorts. Same problem with Json, dates are cast as strings but was thinking of adding a UDF to determine if a Json string could be interpreted as a date. Also, when the text file is written you will see something like


[items][0]   OBJECT   *obj17424* 


Is there anything of significance to the OBJECT value [same seems to apply to ARRAY]?


Finally, since the .json is an array is there a WB like


foreach Date in Items
   process
Next

Code (WINBATCH) Select Expand



;Winbatch 2021C - Test of new Json Extender
;uses  json text file
;Stan Littlefield, September 12, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender('ilcjs44i.dll', 0, 'ilcjs64i.dll')


cJson = Dirscript():"dow.json"
If !FileExist(cJson) Then Terminate(@TRUE,"Cannot Continue... missing file",cJson) 
BoxOpen("Parsing:":cJson,"Please Wait...")
cFile = Dirscript():"parsed_dow.txt" 
If FileExist(cFile) Then FileDelete(cFile) 
jdata = FileGet(cJson)


If jsValid(jdata)
   jstypes = crtypes()
   Object = jsParse(jdata)
   crTree()
   jsObjClose()
   Message("Json Tree Created",cFile)
Else 
   jsObjClose()
   Message(cJson,"Not Valid Json")
Endif


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine crTree()
   output = "node":@tab:"type":@tab:"value":@CRLF
   tree =jsKeyPaths(Object,"")
   Cnt = ItemCount(tree, @tab)
   ;Message("",Cnt)
   For i = 1 to Cnt
      iVal = ""  ;default
      item = ItemExtract(i,tree, @tab)
      iType = jsValueType(Object,item)
      iVal = jsValueGet(Object,item)
      output := item:@tab:MapKeyFind(jstypes,iType,'NULL'):@tab:iVal:@CRLF
   Next
   FilePut(cFile,output) 
   Return(1)
#EndSubRoutine


#DefineSubRoutine crtypes()
maps = $"1,NULL
2,BOOL
4,NUMBER
8,STRING
16,ARRAY
32,OBJECT
$"
jstypes = MapCreate(maps,',',@lf)
Return(jstypes)
#EndSubRoutine


Return


[td]

There are several concepts that you need to get familiar with.

JSON objects are containers. The extender stores the objects by references. The *Objxxxx* string you see is the index, a.k.a., handle into the extender's object reference storage area. This means that variables containing *objxxxx* are what you use in the extender functions that have the object-handle first parameter. This handle concept is nothing new. Many WIL extenders use the same approach. As the help file mentions, " JSON handles are unique identifiers use by the extender to store and track JSON data as key/value pairs. JSON handles are used by other extender functions to query, modify, and convert the JSON data the handle represents."

A JSON array is not a WIL array or C# array, and remember JSON data is stored as key/value pairs. Key/value pairs are basic to JSON because JSON represents one or more javascript objects which are classes with data access provided by a name.  This concept is common to all Object Oriented programming languages including javascript.

If you pass an array name (with path if applicable) to the jsValueGet function's key-name parameter, you will get a coma delimited item list of array element values. You can query individual array element values by appending the index of the targetted value to the key-name parameter. For example, use "[somearraykey][0]" to obtain the value of the first element of the array "somearraykey".

More from the help file docs for jsValueGet:
"This function retrieves the value of a key/value pair contained within the passed in a JSON object, one of its child objects, or array contained within the object or sub-object. If the key's value is a JSON object (@JsonObj), the returned value will be a JSON extender object handle. If the returned key's value is a JSON array (@JsonArr), and a bracketed array index is not a postfix to the key name, the return value will be comma delimited list of array element values."



The extender is new but its implementation is fairly straightforward to WIL extender users once you familiarize yourself with the help file and practice a bit.

[stanl]

There are several concepts that you need to get familiar with.

and one concept you might have an issue with.... I'm not as stupid as you might think.   I already do my Json data with Powershell and a few lines of code [including the foreach{ }. I like the Extender. Just tried to provide a workable script with data, which others might try and raise their own questions.

[td]

and one concept you might have an issue with.... I'm not as stupid as you might think.   I already do my Json data with Powershell and a few lines of code [including the foreach{ }. I like the Extender. Just tried to provide a workable script with data, which others might try and raise their own questions.

No way that  I would ever think you are "stupid" and I appreciate your questions as they may be of some help to others. Since you are a Powershell fan go for it. 

[td]

The jsObjMap function is not intended to be used to dump the entire contents of a JSON tree. It is intended to be a quick way to access a single object. For example, I have a use for the function in WebBatch script where I need to extract several values from a simple JSON object and use those values to construct a JSON object as a reply to the site that sent the original JSON.

You can use the results from jsKeyPath to pull all the values from a JSON tree and then use jsValueGet in a for loop to dump values if that is what you want.

[stanl]

All is good. Don't want to go to war over this[Vietnam was enough for me]. My ultimate goal with this is to initially create a 'schema' of sorts for Json data received on a regular basis. I create UDF's to hopefully put the interpreted data into Excel or a DB table.  The code and sample I posted pretty much do that and the Extender is top notch. From that .txt output I see that

   
• items ==> multiple node values like [Date]

and so here is where I go into syntax hell using terms inaccurately .... I look like items as more a 'collection' with sub-objects, in this case [Date] for this particular type of Json.


As was brought out initially, maps are distinct key:value pairs, so any kind of mapping to drill down into a collection for those sub-objects is pointless.


So: my question about a foreach loop for items indicates my particular need to parse Json into a table or spreadsheet which is not the intent of the Extender.


Finally, this is not a Powershell vs. WB contest.

[JTaylor]

Assuming I am understanding the discussion, I would agree with Stan that having the ability to create a Map and use a ForEach loop for elements/members on the same level would be very nice.  It is so much cleaner and  smoother than having to use a For loop or a delimited string.   I solved the unique key issue by using sequential numbers.   Trying to remember if it was one-based or zero-based.   Several upsides is the the ForEach option, of course, You can easily address a particular element/member by position.   Can use a For loop if preferred.   Referencing by number or string results in the same outcome.    Map[0] = Map["0"]. 

I would put forth this as a suggestion.   Speaking form experience, this works very well and I can't see it being outside the realm of what you are already doing or that hard to implement.  I am guessing it would get a lot more use than a TAB delimited string.

Thanks.

Jim

[td]

We have already considered several options before releasing the first experimental version of the extender. One was to create a map with the JSON paths as keys and the JSON value pointed to by each path as the value of in the map.  Another consideration was to create a single rank array with the same content as the item list output of the jsKeyPaths function.  Many WinBatch users don't realize that you can cycle through single rank arrays using a WIL ForEach loop. Also, while WIL permits the use of numbers as WIL Map keys it is not something that is encouraged because experience has indicated that it can lead to user confusion under certain conditions. Also, regular WIL arrays will accept strings as indexes as long as the string converts to a number.

None of the options is particularly difficult to code conceptually which is a bit disappointing because that takes all the fun out of it. It does take some time because of the amount of testing new functions and features typically require to conform to the self-set requirements of ILC software. When features are being considered it is the custom to look at the cost versus benefit. The cost side of the equation involves a lot more than just development cost. So a few questions need to be asked and properly answered before tarting up the extender with more functionality.

[kdmoyers]

<< ...before tarting up the extender... >>
This gave me the Monday morning chuckle I needed!!   :D

-Kirby

[JTaylor]

Okay.   I thought the point of this type of release was to see how well what you did works and to get ideas for changes that might be useful.  Guess I misunderstood.   Also, didn't think of what I was suggesting as new functionality....Just a way to present the data in a more easily accessible fashion.    If something works well I can't see not going with because someone might get confused at some point.   We all get confused by something at some point.   Doesn't mean the thing is poorly conceived or implemented.    Just because someone doesn't like my cooking doesn't mean there is, necessarily, anything wrong with it.   Could just be their problem.

Jim

[td]

No, you did not misunderstand the purpose of this pre-release. But there does seem to be some misunderstanding concerning the points I was trying to make above. It could very well be because I didn't do a good job of making the points which is often the case. At any rate, ILC deals with multiple users almost every day with many different backgrounds and skill levels. Most never post on this forum. It is impossible to please everyone but we do pay attention to what users have to say about what works, what needs they have, and what causes problems. The hardest part of software development is the design, not the coding, and design is where the accumulated user feedback is invaluable. Good software design makes a stab at the right balance of many considerations.

[JTaylor]

Understood.

Jim

[stanl]

To address Tony's partial remarks: At any rate, ILC deals with multiple users almost every day with many different backgrounds and skill levels. Most never post on this forum. It is impossible to please everyone
Below is a sample of the type of Json processing I do or will need to do; mainly large return files that have to be parsed for specific 'field values' / 'column values' as inserted into either a table or workbook. I picked an API reference to census variable codes as it approximates my needs. The Json return file is just under 13mg and has around 250k nodes. The script will

   
• download the Json data as .json file
• process first 100 nodes for sample output
• format: node / type / value separated by tab.

The Extender is excellent at getting the Json and preparing the node count. The output to text loop is slow but that is my problem and time taken will not be a factor for me [but would appreciate suggestions]. I chose to use file functions rather than holding data in memory for final FilePut() just given the size of the data. Of course I will figure out a UDF to get data like this [with repeated node names] into a table then it just a matter of handling in SQL.

Bottom line: thumbs up for the Extender and large json returns.

Code (WINBATCH) Select Expand



;Winbatch 2021C - Census Data - tests large json return
;uses  WinHttp.WinHttpRequest.5.1 for JSON return
;Stan Littlefield, September 14, 2021
;======================================================================================================
IntControl(73,1,0,0,0)
Gosub udfs
AddExtender('ilcjs44i.dll', 0, 'ilcjs64i.dll')
cUrl = "https://api.census.gov/data/2019/acs/acs1/variables.json"
BoxOpen("Parsing:":cUrl,"Please Wait...")
cFile = Dirscript():"CensusAPI.txt"
cJson = Dirscript():"CensusAPI.json"
If FileExist(cFile) Then FileDelete(cFile)
If FileExist(cJson) Then FileDelete(cJson)       
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.SetRequestHeader("Content-Type", "application/x-www-form-urlencoded")
request.SetRequestHeader("Accept", "application/json")
request.Send()
jdata = request.ResponseText
request = 0


;persist .json file
FilePut(cJson,jdata)
;=================================
If jsValid(jdata)
   jstypes = crtypes()
   Object = jsParse(jdata)
   BoxTitle("Creating text Output ":cFile )
   crTree()
   jsObjClose()
   BoxShut()
   Message("Json Tree Created",cFile)
Else 
   jsObjClose()
   BoxShut()
   Message(cJson,"Not Valid Json")
Endif


Exit


:WBERRORHANDLER
geterror()
Terminate(@TRUE,"Error Encountered",errmsg)
Exit


:udfs
#DefineSubRoutine geterror()
   wberroradditionalinfo = wberrorarray[6]
   lasterr = wberrorarray[0]
   handlerline = wberrorarray[1]
   textstring = wberrorarray[5]
   linenumber = wberrorarray[8]
   errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
   Return(errmsg)
#EndSubRoutine


#DefineSubRoutine crTree()
   Fx = FileOpen(cFile,"Write")
   output = "node":@tab:"type":@tab:"value"
   FileWrite(Fx,output)
   tree =jsKeyPaths(Object,"")
   Cnt = ItemCount(tree, @tab)
   ;Message("",Cnt)
   For i = 1 to Cnt
      iVal = ""  ;default
      item = ItemExtract(i,tree, @tab)
      iType = jsValueType(Object,item)
      iVal = jsValueGet(Object,item)
      iType =MapKeyFind(jstypes,iType,'NULL')
      If iType<>"OBJECT"
         output = item:@tab:iType:@tab:iVal
         BoxText("Processing item ":i:" of ":Cnt)
         FileWrite(Fx,output)
      Endif
      If i>100 Then Break  ;quick test
   Next
   FileClose(Fx)
   Drop(Fx)
   Return(1)
#EndSubRoutine


#DefineSubRoutine crtypes()
maps = $"1,NULL
2,BOOL
4,NUMBER
8,STRING
16,ARRAY
32,OBJECT
$"
jstypes = MapCreate(maps,',',@lf)
Return(jstypes)
#EndSubRoutine


Return


[td]

For the next version of the extender, jsKeyPaths will return an array instead of an item list. This will break your script but the fix would be simple enough. Using an array to spin through a large data set instead of an item list should improve performance. The performance change may be significant but that will not be known with certainty until the new implementation is benchmarked. 

[edit]  Your script could be useful for benchmarking.

[td]

Ran a bench test comparing the current item list paths with the new array paths jsKeyPaths function using your script as the basis for the bench scripts. The performance improvement of using an array instead of an item list is only about 2% on a 5000 file record test.  It appears that the jsValueType and jsValueGet functions are consuming a good deal of CPU time because of all the path permutations that are considered. Path parsing is computationally intensive but there might be some opportunities to improve it.

The real-world example has been helpful. Thanks for providing it.

[td]

Using the "CensusAPI" script's first 5000 entries as a benchmark, it was possible to demonstrate a significant improvement in the extender's performance. The 5000 record benchmark went from 13 minutes to 2 seconds after a few extender modifications. That is a whopping 99.8% decrease in execution time.

Nothing quite like thoughtful user feedback. Thanks again.

[stanl]

Using the "CensusAPI" script's first 5000 entries as a benchmark, it was possible to demonstrate a significant improvement in the extender's performance. The 5000 record benchmark went from 13 minutes to 2 seconds after a few extender modifications. That is a whopping 99.8% decrease in execution time.

Nothing quite like thoughtful user feedback. Thanks again.

So, an Extender upgrade. or .....  :o

[td]

or be patient. 

[stanl]

or be patient.

of course.  I should have just said congrats...