Reading a JSON and looping
Hello guys,
I'm getting from the server a JSON string with the statuses of a particular actions. In this case it returns results for 2 actions.
For
ID: 551720
and
ID: 551721
String looks like this:
HTML Code:
[{"ElectronicId":551720,"DocumentNr":"130/10/15","DocumentTypeId":1,"DocumentTypeName":"eInvoice","StatusId":30,"StatusName":"Sent","RecipientBusinessNumber":"0050960000","RecipientBusinessUnit":"","RecipientBusinessName":"Comp d.o.o.","Created":"2019-07-23T21:21:23.743","Updated":"2019-07-23T21:21:24.587","Sent":"2019-07-23T21:21:24.587","Delivered":null},{"ElectronicId":551721,"DocumentNr":"130/10/15","DocumentTypeId":1,"DocumentTypeName":"eInvoice","StatusId":30,"StatusName":"Sent","RecipientBusinessNumber":"00509605454","RecipientBusinessUnit":"","RecipientBusinessName":"Comp d.o.o.","Created":"2019-07-23T21:23:05.887","Updated":"2019-07-23T21:23:07.043","Sent":"2019-07-23T21:23:07.043","Delivered":null}]
Sometimes it returns 1, sometimes 2, or maybe 20 statuses (different "ElectronicId")
How could I loop within JSON.
I have a code that works when I have only 1 response, but it doesn't work when I have more than 1.
Here is the code for 1 response:
Code:
Dim cJS As New clsJasonParser
cJS.InitScriptEngine
results = """""here goes the JSON string""""""
Set JsonObject = cJS.DecodeJsonString(CStr(result))
Debug.Print cJS.GetProperty(JsonObject, "ElectronicId")
Debug.Print cJS.GetProperty(JsonObject, "DocumentNr")
Debug.Print cJS.GetProperty(JsonObject, "DocumentTypeId")
Debug.Print cJS.GetProperty(JsonObject, "DocumentTypeName")
Debug.Print cJS.GetProperty(JsonObject, "StatusId")
Here is the code for the clsJasonParser bClass:
Code:
Option Explicit
Private ScriptEngine As ScriptControl
Public Sub InitScriptEngine()
Set ScriptEngine = New ScriptControl
ScriptEngine.Language = "JScript"
ScriptEngine.AddCode "function getProperty(jsonObj, propertyName) { return jsonObj[propertyName]; } "
ScriptEngine.AddCode "function getKeys(jsonObj) { var keys = new Array(); for (var i in jsonObj) { keys.push(i); } return keys; } "
End Sub
Public Function DecodeJsonString(ByVal JsonString As String)
Set DecodeJsonString = ScriptEngine.Eval("(" + JsonString + ")")
End Function
Public Function GetProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Variant
GetProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Public Function GetObjectProperty(ByVal JsonObject As Object, ByVal propertyName As String) As Object
Set GetObjectProperty = ScriptEngine.Run("getProperty", JsonObject, propertyName)
End Function
Re: Reading a JSON and looping
I'm a little bit closer to the solution, but I have another challenge.
This is the code that I now have:
Code:
cJS.InitScriptEngine
Set JsonObject = cJS.DecodeJsonString(CStr(result))
Set JsonObject = cJS.GetObjectProperty(JsonObject, "ROOT")
Keys = cJS.GetKeys(JsonObject) ' svi kljuèevi iz Json stringa
For lRow = 1 To UBound(Keys)
Set Jitem = cJS.GetObjectProperty(JsonObject, Keys(lRow - 1))
Debug.Print cJS.GetProperty(Jitem, "ElectronicId")
Debug.Print cJS.GetProperty(Jitem, "DocumentNr")
Debug.Print cJS.GetProperty(Jitem, "DocumentTypeId")
Next lRow
But because the JSON doesn't have the "key" only the ROOT I get error here:
Code:
Set JsonObject = cJS.GetObjectProperty(JsonObject, "ROOT")
Once again here is the JSON string:
Code:
[{"ElectronicId":551720,"DocumentNr":"130/10/15","DocumentTypeId":1,"DocumentTypeName":"eInvoice","StatusId":30,"StatusName":"Sent","RecipientBusinessNumber":"0050960000","RecipientBusinessUnit":"","RecipientBusinessName":"Comp d.o.o.","Created":"2019-07-23T21:21:23.743","Updated":"2019-07-23T21:21:24.587","Sent":"2019-07-23T21:21:24.587","Delivered":null},{"ElectronicId":551721,"DocumentNr":"130/10/15","DocumentTypeId":1,"DocumentTypeName":"eInvoice","StatusId":30,"StatusName":"Sent","RecipientBusinessNumber":"00509605454","RecipientBusinessUnit":"","RecipientBusinessName":"Comp d.o.o.","Created":"2019-07-23T21:23:05.887","Updated":"2019-07-23T21:23:07.043","Sent":"2019-07-23T21:23:07.043","Delivered":null}]
Does anyone have any ideas?
Re: Reading a JSON and looping
Quote:
Originally Posted by
Davor Geci
Does anyone have any ideas?
Just use cJS.GetObjectProperty(JsonObject, 0) and cJS.GetObjectProperty(JsonObject, 1) to access first and second element of the array.
Edit: Yes, I tried it. Although you had errors in the code above, I had to escape the JSON to put it in the "here goes the JSON string" placeholder and worst of all Set ScriptEngine = New ScriptControl was compiling *only* if Microsoft Script Control 1.0 is added in Project->References menu (not Components) which is non-obvious.
I personally wouldn't load the whole JScript engine to parse some tiny JSON snippet only. Try [VB6/VBA] JSON parsing to built-in VBA.Collections for one light-weight solution.
cheers,
</wqw>
