Loading JSON into Array - looping the Array

[nuclearsiloman]

I use your class all the time mainly rs>json piece, but I seem to be struggling in parsing a json string into an array and then iterating through the array. For example, I use the rs>json piece to write the table definition to a file:

{"data":[{"column_name":"id","COLUMN_DEFAULT":null,"DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null},{"column_name":"idadmin","COLUMN_DEFAULT":"((0))","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null},{"column_name":"adminname","COLUMN_DEFAULT":null,"DATA_TYPE":"nvarchar","CHARACTER_MAXIMUM_LENGTH":50},{"column_name":"adminpassword","COLUMN_DEFAULT":null,"DATA_TYPE":"nvarchar","CHARACTER_MAXIMUM_LENGTH":100},{"column_name":"adminlevel","COLUMN_DEFAULT":null,"DATA_TYPE":"nvarchar","CHARACTER_MAXIMUM_LENGTH":100},{"column_name":"lastlogin","COLUMN_DEFAULT":null,"DATA_TYPE":"datetime","CHARACTER_MAXIMUM_LENGTH":null},{"column_name":"pcSecurityKeyID","COLUMN_DEFAULT":"((0))","DATA_TYPE":"int","CHARACTER_MAXIMUM_LENGTH":null},{"column_name":"adm_ContactName","COLUMN_DEFAULT":null,"DATA_TYPE":"nvarchar","CHARACTER_MAXIMUM_LENGTH":250},{"column_name":"adm_ContactEmail","COLUMN_DEFAULT":null,"DATA_TYPE":"nvarchar","CHARACTER_MAXIMUM_LENGTH":250}]}

I then send the file via msxml to another server that picks it up, reads it and assigns the string to 'x'.

set tableDef = New JSONobject tableDef.parse(x)

If I do the following, then the string is output to the screen, so I think all is good? tableDef.Write()

When I try to use your example of iterating through it, it returns nothing.

[nuclearsiloman]

OK, so I'm parsing using this:

set oJSON = JSON.parse(x) for each item in oJSON.data response.write item.column_name & "
" next

I do not know the item names in advance so the above working example I knew it was 'column_name'. Is there a way to reference the position?

[rcdmk]

For properties, there is no way to get them by index. Is there any reason you wouldn't know the property names upfront? If you know the index upfront, you can surely know the property name, not?

Sorry if I'm mistaken.

[nuclearsiloman]

The json contains dB table structures so every table has different column names. What about using Pairs?

This would work for me but I couldn't get that to work either. Using the supplied string in my original post, would you be so kind to show me how?

Thankyou.

[rcdmk]

Of course. That would be no problem.

In your case, if the original string is an array representation, the parse method will return a jsonArray object and thus object has a property called items that you can iterate on.

In case it is exactly that string you posted, it is an object representation and thus the parse method loads the data into the object and returns it. Your object has a data property that is a jsonArray and you can iterate its items property:

<%
' ...
Set JSON = new jsonObject
JSON.Parse(jsonString)

Set arr = JSON("data")
For each item in arr.items
    Response.Write item("column_name") & "<br>"
Next

' for JSON array strings
Set arr = JSON.Parse(jsonArrString)
For each item in arr.items
    Response.Write item("column_name") & "<br>"
Next
%>

Hope this helps. Bests.

[nuclearsiloman]

Thanks, but this output nothing. Can this be done with .Pairs?

[rcdmk]

Yes, you can use the .Pairs property on the JSON object if you don't know a property name in advance. It is an array of JSONPairs:

<%
' ...
Set JSON = new jsonObject
JSON.Parse(jsonString)

Set arr = JSON("data")
For each item in arr.items
    Set props = item.Pairs ' list of JSONPair objects
    Set column = props(0) ' first property of each item
    Response.Write column.name & " = " & column.value "<br>"
Next

But in your case, the JSON representation you shared in the firs message has the same property names, so the column_name property will always be there and you can use my previous example (of course, replacing jsonString with your JSON data or the name of the variable that it is holding it).