Another ParseJSON question - array of Dictionaries

General
1

Even though I’ve used Xojo for many years, there are parts of it I have seldom and sometimes have never had a need for. Up until now I’ve never needed to parse JSON and quite honestly I rarely use Dictionaries in most of the apps I create so combining the two has me a bit confused about the proper syntax to get what I want. I’ve been searching through other posts and what I have found either doesn’t work for my case or I lack some basic ability to connect the dots from what I’m reading.

With that said I have anew system that has recently been installed in our manufacturing environment that I need to extract data from using the vendor’s REST API, and the data is returned as JSON. There a lot of options for what to retrieve but what I’m working with right now is a list of press activities during a specific time frame. The retrieving the JSON part is easy, but with all our other systems what I get is XML not JSON and I’m struggling to loop through the data to make it usable in a table I need to display and calculations to graph some of the activity values.

Here is a small sample of the JSON I get back from this system’s REST API:

{“error”:null,“pageInfo”:{“offset”:0,“pageSize”:5},“activities”:[{“id”:null,“name”:null,“timeTypeName”:null,“timeTypeGroupName”:null,“workstep”:null,“device”:{“id”:“114”,“name”:“Heidelberg_1”,“classId”:“ID_SheetfedPress”},“goodCycles”:0,“wasteCycles”:0,“startTime”:“2021-04-06T10:04:41-05:00”,“endTime”:“2021-04-06T10:04:41-05:00”,“comment”:null,“costCenter”:null,“employees”:[{“id”:“50059”,“name”:“Raleigh”,“firstName”:“Jason”}]},{“id”:“4100”,“name”:“Good production”,“timeTypeName”:“Execution time”,“timeTypeGroupName”:“Production time”,“workstep”:{“id”:“_210402_132438407_015664”,“name”:“Sheet_2 4/4”,“job”:{“id”:“030008-21”,“name”:“2021 Jefferson Middle School”}},“device”:{“id”:“114”,“name”:“Heidelberg_1”,“classId”:“ID_SheetfedPress”},“goodCycles”:405,“wasteCycles”:22,“startTime”:“2021-04-06T10:02:01-05:00”,“endTime”:“2021-04-06T10:04:41-05:00”,“comment”:null,“costCenter”:“114”,“employees”:[{“id”:“50059”,“name”:“Raleigh”,“firstName”:“Jason”}]},{“id”:“4000”,“name”:“Basic makeready”,“timeTypeName”:“Setup time”,“timeTypeGroupName”:“Production time”,“workstep”:{“id”:“_210402_132438407_015664”,“name”:“Sheet_2 4/4”,“job”:{“id”:“030008-21”,“name”:“2021 Jefferson Middle School”}},“device”:{“id”:“114”,“name”:“Heidelberg_1”,“classId”:“ID_SheetfedPress”},“goodCycles”:0,“wasteCycles”:45,“startTime”:“2021-04-06T10:00:37-05:00”,“endTime”:“2021-04-06T10:02:01-05:00”,“comment”:null,“costCenter”:“114”,“employees”:[{“id”:“50059”,“name”:“Raleigh”,“firstName”:“Jason”}]},{“id”:null,“name”:null,“timeTypeName”:null,“timeTypeGroupName”:null,“workstep”:null,“device”:{“id”:“114”,“name”:“Heidelberg_1”,“classId”:“ID_SheetfedPress”},“goodCycles”:0,“wasteCycles”:0,“startTime”:“2021-04-06T10:00:36-05:00”,“endTime”:“2021-04-06T10:00:37-05:00”,“comment”:null,“costCenter”:null,“employees”:[{“id”:“50059”,“name”:“Raleigh”,“firstName”:“Jason”}]},{“id”:“4100”,“name”:“Good production”,“timeTypeName”:“Execution time”,“timeTypeGroupName”:“Production time”,“workstep”:{“id”:“_210401_153703613_002173”,“name”:“Sheet_5 4/4”,“job”:{“id”:“042732-21”,“name”:“2021 Eisenhower Middle School”}},“device”:{“id”:“114”,“name”:“Heidelberg_1”,“classId”:“ID_SheetfedPress”},“goodCycles”:0,“wasteCycles”:0,“startTime”:“2021-04-06T10:00:00-05:00”,“endTime”:“2021-04-06T10:00:36-05:00”,“comment”:null,“costCenter”:“114”,“employees”:[{“id”:“50059”,“name”:“Raleigh”,“firstName”:“Jason”}]}]}

I have several tools to make this JSON more readable and I know the top level three values, error which will always be empty when the query is valid, pageinfo which is an array of values and the thing I need to actually use an Array that contains other single values and arrays. I created a property in the test window I’m working with

Public Property jsonDict As Dictionary

so that I can view the contents in the debugger. What I see when when I run this line of code

jsonDict = ParseJSON(jsontext)

and look at the variable in the debugger is that error= nill, pageinfo = Dictionary and activities = Object(4) which is an array of 5 Dictionaries. Here is where I kind of can’t figure out what to do to loop through the array and get the values I need. I was wanting to make it a dictionary of Dictionaries by itself but that fails when I try this:

If jsonDict.HasKey("activities") Then
    Var pressactivities As Dictionary = jsonDict.Value("activities")
End If

Which gives me an illegal cast exception.

So any help on how to approach this? What I want is for each dictionary in Activities, I want to create an array of values in columns pulled from the dictionary and any dictionaries it contains which I’ll display in a listbox. I’m probably losing it because it’s late in the day and might see it differently in the morning, but any advice you can give on how to loop through an array of Dictionaries like in my sample JSON to pull specific values would be immensely helpful.

2

In 2021R1, JSONItem is backed by ParseJSON, so you can do all of this with JSONItems and get the speed benefits that you used to only get with ParseJSON. The following will give you an idea of how to traverse the JSONItem created from your returned JSON string:

var j as new JSONItem( jsontext )
if j.HasKey( "activities" ) then
  var activities as JSONItem = j.Value( "activities" )
  if activities.IsArray then
    var currentActivity as JSONItem
    var intMax as Integer = activities.LastRowIndex
    for intCycle as Integer = 0 to intMax
      currentActivity = activities.ValueAt(intCycle)
      break
    next
  end if
end if
3

For a different perspective, I’m going to disagree slightly in that that, if you’re writing new code, there is no advantage to using JSONItem and may only confuse the issue. The code @Anthony_G_Cyphers posted is almost identical with the results of ParseJSON.

1 Like
4

Or

Var v As Dictionary = ParseJSON(jsontext)
Var acts() As Object = v.Lookup("activities", New Dictionary())
For i As Integer = 0 to acts.LastIndex
  Var act As Dictionary = Dictionary(acts(i))
  // process activity
Next
1 Like
5

I was under the impression that Dictionaries with ParseJSON had additional overhead compared to JSONItems. Probably not, though, now that I think about it. It’s probably all dictionaries internally with a different API. Bah.

6

Further reading that may help:
https://documentation.xojo.com/topics/file_managment/reading_and_writing_data_in_json_format.html

7

Xojo made its JSON object a mixed tree of Arrays and Dictionaries that depends on the user design.

8

Thanks! I love this Forum!

I’ll try Rick’s solution in the morning. I purposely left my work laptop at home so I wouldn’t get sucked into coding tonight. Rick’s code snippet makes total sense to me, but that may be because I’ve relaxed and had a glass of wine since I got home.

2 Likes