JSON Array size

[bottomleypotts]

Trying to use the new JSON extender. I need to find out how many elements exist in an array. How should I do this? Previously with the scriptcontrol method, I would just return the "array.length" and this would tell me how many elements there are. Seems like I am fighting the json to get the answer, and hope I am missing an easy way to solve the problem.

Also while I am here, can I request the option for a default value for a jsValueGet? Same way it has been done with maps. Otherwise I have to search the array first to make sure I have the correct json first which becomes tedious!

[stanl]

Post or Upload example data. WB Json extender is not a completed project [as I understand]; Jim Taylor has his Json Extender and PS has Json functions which can be called from CLR. Now, assuming you would be importing Json data w/out a known structure, then how do you determine a specific Json element is an array?  Will make this an interesting thread.

[td]

Trying to use the new JSON extender. I need to find out how many elements exist in an array. How should I do this? Previously with the scriptcontrol method, I would just return the "array.length" and this would tell me how many elements there are. Seems like I am fighting the json to get the answer, and hope I am missing an easy way to solve the problem.

Pass the array handle to jsConMap so you can use WIL array functions and syntax to process the content.

Also while I am here, can I request the option for a default value for a jsValueGet? Same way it has been done with maps. Otherwise I have to search the array first to make sure I have the correct json first which becomes tedious!

Not sure what you are requesting here. If you convert the array to a WIL map, it shouldn't be an issue.

[td]

Post or Upload example data. WB Json extender is not a completed project [as I understand]; Jim Taylor has his Json Extender and PS has Json functions which can be called from CLR. Now, assuming you would be importing Json data w/out a known structure, then how do you determine a specific Json element is an array?  Will make this an interesting thread.

The JSON extender has been in release for several months. It has been downloaded many times and there have been no bug reports or new enhancement requests other than the above. Of course, all extenders are never completed in the sense that there is always interest in making improvements that are of real (not just perceived) benefit to the user community.

Granted it takes some know-how and is not for the faint of heart but since PS functionality is mostly just .Net scripting much if not most PS functionality can be converted to WIL CLR hosting. 

[stanl]

So.. maybe reduce the thread to a coding example.  Simple well-formed Json

Code Select Expand


{
  "Contoso": {
    "Accounts": {
      "Users": [
                                {
                                                "jdoe": [
                                                                {             
                                                                                "givenName": "John",
                                                                                "surname": "Doe",
                                                                                "department": "Finance"
                                                                }
                                                ],
                                                "jdoe2": [
                                                                {             
                                                                                "givenName": "Jane",
                                                                                "surname": "Doe",
                                                                                "department": "Marketing"
                                                                }
                                                ],
                                                "asmith": [
                                                                {             
                                                                                "givenName": "Alice",
                                                                                "surname": "Smith",
                                                                                "department": "IT"
                                                                }
                                                ]
                                }
                  ]
                }
  }
}







so, if not knowing that much about the structure [meaning data is obtained from API or a file] how we code for

   
• how many accounts/users
• how many elements in each user
• how to iterate results

... paste code into text.json somewhere




Json I currently work with has a 'parent' value but also a total indicator, so a 'je ne sais quoi' iteration of array elements would be interesting.

[stanl]

P,S. - on my previous post, my intention was not to knock the Json extender as not completed in a strict sense but as Tony alluded, anything can be changed.  Json has been touted as simpler and more elegant than older xml. Sure! It isn't [ just .02 ]

[td]

You have to know something about the JSON you download or you wouldn't know to download it in the first place and most Websites provide a schema so I don't believe the premise is entirely valid. If it is, you can certainly figure out JSON contents using one of several extender enabled techniques but for efficiency's sake let's let the OP drive the discussion.

[stanl]

but for efficiency's sake let's let the OP drive the discussion.

Agree 100%.... you da man

[bottomleypotts]

Code (winbatch) Select Expand


AddExtender(`ilcjs44i.dll`,0,`ilcjs64i.dll`)


#DefineFunction jGet(j,k)
If j==`` || k==`` Then Return ``
o=CreateObject(`MSScriptControl.ScriptControl`)
o.Language=`JScript`
Rc=ErrorMode(@OFF)
Ret=o.Eval(:`var obj=`:j:`;obj.`:k)
ErrorMode(Rc)
Return ``:Ret
#EndFunction


jdata=`{"id":"2022 MCC Championship","name":"2022 MCC Championship","location":null,"country":null,"website":null,"rules":"STANDARD","chess960":"STANDARD","timecontrol":null,"rounds":[{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0}],"eboards":["45539","45762"]}`

If jsValid(jdata)
T = jsParse(jdata)
MapT = jsConMap(T, @JsonValue)
R=jsValueGet(T, `rounds`)
MapR = jsConMap(R, @JsonValue)

a=ArrInfo(MapR,1)
Endif

jsConClose()


j=jGet(jdata,`rounds.length`)
k=jGet(jdata,`rounds.something`)

Exit



Here is my code.

Forgot I can use some Array functions on Maps. Still seems like j is easier to process. And k easier to handle, because it doesn't error. Most of the JSON I work with is not documented.

Have my solution! Thanks for listening.

[td]

Based on the code you posted and your original post it would appear that you do not have a clear understanding of how to use the JSON Extender's functions. But since you are happy with your current solution, it does matter much.

[bottomleypotts]

Based on the code you posted and your original post it would appear that you do not have a clear understanding of how to use the JSON Extender's function. But since you are happy with your current solution, it does matter much.

Happy to learn something. What am I clearly missing?

[bottomleypotts]

While I am here, shouldn't a jsValueGet return a blank string or a @NULL when the value is 'null'? Rather than return the string 'null'?

[stanl]

Maybe a sidebar but if you are using MSScriptControl.ScriptControl for jscript/json.... type in getjson in the search bar on this section. Should return several post with additional jscript functions related to processing json.  Most of these relate to moving API json returns into Excel but might be of some help


PS.7 [for core] now allows output of Json to a hash table... easier to obtain keys and values.


Real OT:  I remember working with SOAP before REST where xml data had a schema section and it was a WTF that json didn't.


Will soon be working with the JIRA API... another json challenge: https://developer.atlassian.com/server/jira/platform/jira-rest-api-examples/


.... these threads keep the site lively 8) 8) 8)

[td]

While I am here, shouldn't a jsValueGet return a blank string or a @NULL when the value is 'null'? Rather than return the string 'null'?

Absolutely not. It is an error because almost any data can be a JSON value. You should not be using jsValueGet by itself unless you know the exact path to the JSON pair. You should be using the jsKeyPath function first to establish the path if you don't know it. You then use jsValueGet and jsValueType to obtain information. The path parsing functionality of the extender is the most processor intensive. It is split into a separate function so that you only have to do it once per key/value pair. All the examples in the help file show the correct way to extract information from JSON containers. Also, note that jsKeyPaths returns multiple paths if more than one instance of a key/index/value exists in a JSON container.

There are examples in the extender's help file that illustrate proper usage. Notice that almost the first topic in the help file is "About JSON Paths". This is a strong hint that understanding JSON paths as defined by the extender is very important to using the extender correctly.

It is best to spend some time understanding what the extender help file is trying to communicate to you if intend to use the JSON extender or any extender for that matter.

[bottomleypotts]

Sorry Tony, I have no idea what you're on about!

All I wanted was to know how many rounds there are. How the hell does keypaths help me? The JSON doesn't explicitly tell me how many rounds there are, even though this is a valid piece of information in any chess tournament. Therefore I need to find it. You still have not explained how I should do that in an non convoluted way. In javascript I use rounds.length! BOOM! I have the number. How do I do that in WB?

And how does returning a string "null" help me and not just give me another check that I have to perform. Not that I would ever have a use case where a country could be "null", but in that unlikely event how would I know that I have an actual null value or a string "null" that I expect? And now don't I have to validate every string I extract and remove "null" from any string output?

[stanl]

Sorry Tony, I have no idea what you're on about!
All I wanted was to know how many rounds there are.

Code Select Expand


#Powershell simple json array element count
#assumes element is known

$jData = @"
{"id":"2022 MCC Championship",
"name":"2022 MCC Championship",
"location":null,
"country":null,
"website":null,
"rules":"STANDARD",
"chess960":"STANDARD",
"timecontrol":null,
"rounds":[{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0},
{"count":2,"live":0}],
"eboards":["45539","45762"]}
"@


$json = $jData| ConvertFrom-Json
$json.rounds.count



returns 9 as count. Not sure how the Extender handles this, but trying it by converting to a map may have issues since I believe map keys are unique.

[bottomleypotts]

Not sure how the Extender handles this, but trying it by converting to a map may have issues since I believe map keys are unique.

Thanks Stan. I feel like I'm in good company!

[stanl]

Thanks Stan. I feel like I'm in good company!

Let's not get carried away. All of Tony's comments are more than valid. Below is code I wrote around a year ago for a test of the Extender. It is a query for a census API with a few shots I took at using the Extender functions. My interest was to output a descriptive tree for any given json return, and this was rather large. The output has both the path and value, where the path follows a dot notation which I found I could use to formulate several follow-up scripts. You might find it of some interest:

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
;Updated: September 18, 2021 - based on new Extender using array rather than delimited list
;======================================================================================================
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) ;uncomment if needed
;=================================
If jsValid(jdata)
   ;jstypes = crtypes()  ;no need for map, not persisting node types
   Object = jsParse(jdata)
   Drop(jdata)
   BoxTitle("Creating text Output ":cFile )
   crTree()
   ;crObj()   ;optional test, comment crTree and uncomment this
              ;results will be disappointing but helps with
              ;understanding how Extender functions work
   jsConClose()
   BoxShut()
   Message("Json Tree Created",cFile)
Else 
   jsConClose()
   Drop(jdata)
   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:"value"
   FileWrite(Fx,output)
   tree =jsKeyPaths(Object,"")
   ;tree =jsKeyPaths(Object,"variables")
   ;just for test
   ;Message("Variable Type for tree",VarType(tree)) ;would not expect blank return




   Cnt = Arrinfo(tree,1)
   nMod = Int(Cnt/10)
   i=1
   ForEach item in tree
      If jsValueType(Object,item) <> @JsonObj
         output = item:@tab:jsValueGet(Object,item)
         If (i mod nMod == 0) Then BoxText("Processing ":i:" of ":Cnt)
         FileWrite(Fx,output)
         i +=1
         If i>10000 Then Break
      Endif
   Next
   FileClose(Fx)
   Drop(Fx)
   Drop(tree)
   Return(1)
#EndSubRoutine


#DefineSubRoutine crObj()
   Fx = FileOpen(cFile,"Write")
   output = "node":@tab:"value"
   FileWrite(Fx,output)
   tree =jsKeyPaths(Object,"")
   Cnt = Arrinfo(tree,1)
   nMod = Int(Cnt/10)
   i=1
   brk=@False
   ForEach item in tree
      If jsValueType(Object,item) == @JsonObj
         subtree =jsKeyPaths(Object,item)
         ForEach subitem in subtree
            output = item:@tab:jsValueGet(Object,subitem)
            FileWrite(Fx,output)
            brk=@True
         Next
      Endif
      If brk Then Break
   Next
   FileClose(Fx)
   Drop(Fx)
   Drop(tree)
   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]

Sorry Tony, I have no idea what you're on about!

All I wanted was to know how many rounds there are. How the hell does keypaths help me? The JSON doesn't explicitly tell me how many rounds there are, even though this is a valid piece of information in any chess tournament. Therefore I need to find it. You still have not explained how I should do that in an non convoluted way. In javascript I use rounds.length! BOOM! I have the number. How do I do that in WB?

And how does returning a string "null" help me and not just give me another check that I have to perform. Not that I would ever have a use case where a country could be "null", but in that unlikely event how would I know that I have an actual null value or a string "null" that I expect? And now don't I have to validate every string I extract and remove "null" from any string output?

There are multiple ways to get the number of "rounds" from your JSON example using the extender. Here is but one.

Code (winbatch) Select Expand

AddExtender(`ilcjs44i.dll`,0,`ilcjs64i.dll`)

jdata=`{"id":"2022 MCC Championship","name":"2022 MCC Championship","location":null,"country":null,"website":null,"rules":"STANDARD","chess960":"STANDARD","timecontrol":null,"rounds":[{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0}],"eboards":["45539","45762"]}`
Terminate(!jsValid(jdata), "JSON Example", "Bad JSON text")

hTop = jsParse(jdata)
Rounds = jsKeyPaths(hTop, "count")

Message("Rounds", ArrInfo(Rounds, 1))

jsConClose()


[td]

Using a WIL map.

Code (winbatch) Select Expand

AddExtender(`ilcjs44i.dll`,0,`ilcjs64i.dll`)

jdata=`{"id":"2022 MCC Championship","name":"2022 MCC Championship","location":null,"country":null,"website":null,"rules":"STANDARD","chess960":"STANDARD","timecontrol":null,"rounds":[{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0},{"count":2,"live":0}],"eboards":["45539","45762"]}`
Terminate(!jsValid(jdata), "JSON Example", "Bad JSON text")

hTop = jsParse(jdata)

aPath = jsKeyPaths(hTop, "rounds")
hRounds = jsValueGet(hTop, aPath[0])
mRounds = jsConMap(hRounds)

Message("Rounds", ArrInfo(mRounds, 1))

jsConClose()

Exit

...

[bottomleypotts]

Again, sorry to be argumentative. But it seems stupid to count the number of "count" items in your first example, because I don't have any documentation on the JSON and that keyword could pop up at anytime. And I don't see any difference between your second example and mine. So exactly how have you educated me? Oh, you cut out the code that I was also using to demonstrate obtaining array counting? Are you aware of the tone you give off in your emails?

[td]

It would appear I have not successfully communicated the point. Oh well.

[bottomleypotts]

Code (winbatch) Select Expand


AddExtender(`ilcjs44i.dll`,0,`ilcjs64i.dll`)


#DefineFunction jGet(j,k)
If j==`` || k==`` Then Return ``
o=CreateObject(`MSScriptControl.ScriptControl`)
o.Language=`JScript`
Rc=ErrorMode(@OFF)
Ret=o.Eval(:`var obj=`:j:`;obj.`:k)
ErrorMode(Rc)
Return ``:Ret
#EndFunction


strJson=`{"id":"2022 MCC Championship","name":null}`

Object = jsParse(strJson)

Value = jsValueGet(Object, `name`)
OldValue = jGet(strJson, `name`)

;;;;;

strJson=`{"id":"2022 MCC Championship","name":"null"}`

Object = jsParse(strJson)

Value = jsValueGet(Object, `name`)
OldValue = jGet(strJson, `name`)

jsConClose(Object)

Exit


Here is the use case I am alluding to with null. Yes, the chances of me ever hitting this use case are a billion to one. Doesn't make it good coding.

[stanl]

Here is the use case I am alluding to with null. Yes, the chances of me ever hitting this use case are a billion to one. Doesn't make it good coding.

BP;


Why not go this route. The Extender can identify a null value [1] or a string [8].  Once determined a node has a null value, how you deal with it in terms of further parsing is up to you.

Code (WINBATCH) Select Expand



AddExtender(`ilcjs44i.dll`,0,`ilcjs64i.dll`)
#DefineFunction jGet(j,k)
If j==`` || k==`` Then Return ``
o=CreateObject(`MSScriptControl.ScriptControl`)
o.Language=`JScript`
Rc=ErrorMode(@OFF)
Ret=o.Eval(:`var obj=`:j:`;obj.`:k)
ErrorMode(Rc)
Return ``:Ret
#EndFunction
strJson=`{"id":"2022 MCC Championship","name":null}`
Object = jsParse(strJson)
Value = jsValueType(Object, `name`)
OldValue = jGet(strJson, `name`)
Message(Value,OldValue)


strJson=`{"id":"2022 MCC Championship","name":"null"}`
Object = jsParse(strJson)
Value = jsValueType(Object, `name`)
OldValue = jGet(strJson, `name`)


Message(Value,OldValue)
jsConClose(Object)
Exit


[stanl]

...and while we're on the subject.... below is a modified version of code in a previous reply on this thread. It adds an extra column for element type using a map lookup based on the Extender value types. Although I use Powershell a lot for Json parsing, nothing beats the Extender for iterating a Json tree. The script below works with a large web API Json return but the crTree() function has worked for me with several Json 'schemas' I wished to uncover before writing parsing code. Tony could easily send code for better tree output, but this all goes back to his initial response about understanding how the Extender works.

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
;Updated: September 18, 2021 - based on new Extender using array rather than delimited list
;======================================================================================================
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) ;uncomment if needed
;=================================
If jsValid(jdata)
   jstypes = crtypes()  ;no need for map, not persisting node types
   Object = jsParse(jdata)
   Drop(jdata)
   BoxTitle("Creating text Output ":cFile )
   crTree()
   ;crObj()   ;optional test, comment crTree and uncomment this
              ;results will be disappointing but helps with
              ;understanding how Extender functions work
   jsConClose()
   BoxShut()
   Message("Json Tree Created",cFile)
Else 
   jsConClose()
   Drop(jdata)
   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:"value":@tab:"type"
   FileWrite(Fx,output)
   tree =jsKeyPaths(Object,"")
   ;tree =jsKeyPaths(Object,"variables")
   ;just for test
   ;Message("Variable Type for tree",VarType(tree)) ;would not expect blank return




   Cnt = Arrinfo(tree,1)
   nMod = Int(Cnt/10)
   i=1
   ForEach item in tree
      If jsValueType(Object,item) <> @JsonObj
         output = item:@tab:jsValueGet(Object,item):@tab:jsValueType(Object,item):"(":MapKeyFind(jstypes, jsValueType(Object,item) , '<Unknown>'):")"


         If (i mod nMod == 0) Then BoxText("Processing ":i:" of ":Cnt)
         FileWrite(Fx,output)
         i +=1
         If i>10000 Then Break
      Endif
   Next
   FileClose(Fx)
   Drop(Fx)
   Drop(tree)
   Return(1)
#EndSubRoutine


#DefineSubRoutine crObj()
   Fx = FileOpen(cFile,"Write")
   output = "node":@tab:"value"
   FileWrite(Fx,output)
   tree =jsKeyPaths(Object,"")
   Cnt = Arrinfo(tree,1)
   nMod = Int(Cnt/10)
   i=1
   brk=@False
   ForEach item in tree
      If jsValueType(Object,item) == @JsonObj
         subtree =jsKeyPaths(Object,item)
         ForEach subitem in subtree
            output = item:@tab:jsValueGet(Object,subitem)
            FileWrite(Fx,output)
            brk=@True
         Next
      Endif
      If brk Then Break
   Next
   FileClose(Fx)
   Drop(Fx)
   Drop(tree)
   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


[mattcarl]

Maybe this will help... This is how I solved it, similar to above.

strJson       =  FileGet( nextFileName )           ;;;; the *.json File to use
  Json          = jsParse(strJson)           ;;;; creates an ID for the Object

  Key_01        = '[id]'      ;;;; I know that each "record" in my .json file will have a unique value to be able to count number of records
                                   ;;;; in this example its [id]

  Paths         = jsKeyPaths(Json , Key_01)    ;;;;; creates an array using the [id]
 
  FieldCnt      = ArrInfo(  Paths, 1 )               ;;;;;  ArrInfo returns the number of occurrences of [id]

I actually don't use the Key_01, Paths, FieldCnt beyond this point...
Rather, use the total in a loop to extract data from each record.

Hope that helps someone.