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!
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.