David Brigden
David Brigden

Reputation: 1

VBScript parsing JSON arrays by index using variable

I have to be able to parse JSON strings containing arrays with VBScript. I've been using MSScriptControl within a function to create an object that I can then reference in the VBscript. This has been working great, but I'm seeing examples on newer builds of Win 11 where For Each is throwing errors.

The fix is to use For Next Loops and pass the index of the array element We can reference array elements like this: parsed1.records.[0].id and it works. But If I'm using a variable for the index, like this: parsed1.records.[i].id if fails. The object doesn't support the property or method.

I've been given a fix for this is: .AddCode to the script control that prototypes a myitem() function.

The resulting code looks like this and works:

Dim scrContrl
Set scrContrl = CreateObject("MSScriptControl.ScriptControl")
scrContrl.Language = "Jscript"
scrContrl.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "
Set parsed2 = scrContrl.Eval("(" & sampleString & ")")

Right now, to me that's a magic incantation. I include the magic code and referencing array elements by variable works. I hate hate hate that this is the case. I understand that we're creating a function that will return a given array element. But that's as far as I get. Why can I reference this JSON array by [0] and not by [i]? Also this seems like a hack. Is there a better way to do this?

Following, I need to parse multiple JSON strings and would like to continue using a single function to create the JSON objects instead of multiple inline instances. But when I try this fix with a function I get Catastrophic Failure.

Here's a script I've been using as Proof of Concept

Option Explicit
Dim sampleFile, sampleString
sampleFile = GetScriptDirectory & "\Testing.json"
sampleString = getJsonFromFile(sampleFile)
Dim parsed1, parsed2
Set parsed1 = getJsonObject(SampleString)

Dim scrContrl
Set scrContrl = CreateObject("MSScriptControl.ScriptControl")
scrContrl.Language = "Jscript"
scrContrl.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "
Set parsed2 = scrContrl.Eval("(" & sampleString & ")")
Dim i,j : i = 0 : j = 0

Msgbox "parsed1.records.[0].id: " & parsed1.records.[0].id 'Works
Msgbox "parsed1.records.[i].id: " & parsed1.records.[i].id ' Object doesn't support
Msgbox "parsed1.records.myitem(i).id: " & parsed1.records.myitem(i).id 'Catastrophic Failure

Msgbox "parsed2.records.[0].id: " & parsed2.records.[0].id 'Works
Msgbox "parsed2.records.[j].id: " & parsed2.records.[j].id 'Object doesn't support
Msgbox "parsed2.records.myitem(j).id: " & parsed2.records.myitem(j).id ' Works
Msgbox "parsed2.records.myitem(i).line_items.myitem(j).name: " & _
    parsed2.records.myitem(i).line_items.myitem(j).name 'works


Function GetScriptDirectory()
    GetScriptDirectory = Wscript.CreateObject("WScript.Shell").CurrentDirectory
End Function

Function GetJsonFromFile(jsonFile)
    Dim fso : Set fso = CreateObject("Scripting.FileSystemObject")
    GetJsonFromFile = fso.OpenTextFile(jsonFile).ReadAll
End Function

Function getJsonObject(jsonString)
    ' Create the control for the alternate script engine
    Dim jsonScript
    Set jsonScript = CreateObject("MSScriptControl.ScriptControl")

    ' Set the language to the MS version of javascript
    jsonScript.Language = "Jscript"

    ' add a myitem(i) function for handling arrays
    jsonScript.AddCode "Object.prototype.myitem=function( i ) { return this[i] } ; "
    
    ' Evaluate the JSON string and return the object reference.
    Set getJsonObject = jsonScript.Eval("(" & jsonString & ")")
End Function

And here is the sample JSON that I'm parsing in case anyone is curious

{
    "records": [
        {
            "id": 42,
            "line_items": [
                {
                    "name": "Vogon_Constructor_Fleet"
                },
                {
                    "name": "Heart_of_Gold"
                }
            ]
        },
        {
            "id": 1729,
            "line_items": [
                {
                    "name": "1 and 12"
                },
                {
                    "name": "9 and 10"
                }
            ]
        }
    ]
}

Thanks in advance

Upvotes: 0

Views: 59

Answers (0)

Related Questions