Reading a JSON and looping

[Davor Geci]

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:

[{"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:

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:

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

[Davor Geci]

I'm a little bit closer to the solution, but I have another challenge.

This is the code that I now have:

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:

Set JsonObject = cJS.GetObjectProperty(JsonObject, "ROOT")

Once again here is the JSON string:

[{"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?

[wqweto]

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>