How to access JSON object returned from createObject("htmlfile") in VBS file

Question

I have the following json:

{
   "columns":[
      "b_summary",
      "end_fy",
      "id",
      "isin",
      "market",
      "name",
      "num_employees",
      "sector",
      "ticker"
   ],
   "data":[
      [
         "Microsoft Corp is a technology company. ",
         6,
         59265,
         "US5949181045",
         1,
         "MICROSOFT CORP",
         166475,
         101003,
         "MSFT"
      ]
   ]
}

I've parsed the text using this:

Function parseJson(strJson)
    dim html, window
    Set html = wscript.CreateObject("htmlfile")
    Set window = html.parentWindow
    window.execScript "var json = " & strJson, "JScript"
    Set parseJson = window.json 
End Function

Dim JSON
Set JSON = ParseJson(jsonText)
wscript.echo JSON.columns.length & " columns"
wscript.echo JSON.data.length & " rows"

I can see:

9 columns
1 rows

but I can't see how to access any of the properties under those.

I've tried:

JSON.columns(1), JSON.columns("1"), JSON.columns("[1]"), JSON.columns(1).name, JSON.columns(1).value

which resulted in "Unknown Runtime Error"

And

JSON.columns.item(1), JSON.columns.items(1)

And

dim col
for each col in JSON.columns

which resulted in "Object doesn't support this property or method."

I loaded it with vbsEdit, and it can see the json items listed neatly in their Variable Watcher during runtime, but I don't know how I can access them from my script.

Any idea how to access those properties will be appreciated!

Answer 1

There appears to be no way to reference the JSON array elements using VBScript with the MSHTML JSON parser. One option is to rewrite that part of the script in JScript:

function parseJSON(strJSON) {
  var oHTML = new ActiveXObject("htmlfile");
  var window = oHTML.parentWindow;
  window.execScript("var JSON = " + strJSON, "JScript");
  return window.JSON;
}

var oFSO = new ActiveXObject("Scripting.FileSystemObject");
var oFile = oFSO.OpenTextFile("Test.JSON");
var JSONText = oFile.ReadAll();
oFile.Close();
var oJSON = parseJSON(JSONText);
WScript.Echo(oJSON.columns[0]);
WScript.Echo(oJSON.columns[1]);

Another option is to use the JSON object (Set parser = CreateObject("vbsedit.json")) from the VbsEdit Toolkit. The example included with VbsEdit shows how to reference each array element via a For Each loop, but I did not see an example showing how to directly use an index.