Are you are suddenly sitting at home with nothing to do, due to the corona virus COVID-19? You can’t go to the gym. You can’t go to your favorite computer store to browse all the latest hardware and plan for your next water-cooled build. You can’t go out to eat.
But what you can do is to try out some of the new functionality in HCL Notes and Domino. Today I started a little project where I try to incorporate a number of the latest and coolest functions in a simple but useful application. I hope to be able to create several blog posts about this project over the next few days.
I came up with the idea for this app when someone sent me a link to a web page where information about COVID-19 is aggregated from all US states. I thought it was a neat page, but then I noticed that they have a public API where the numbers are served up. Now things started to become much more interesting! This is what the JSON data looks like:
[ { "state": "AK", "positive": 6, "negative": 400, "pending": null, "death": null, "total": 406, "lastUpdateEt": "3/18 16:30", "checkTimeEt": "3/19 15:12" },{ "state": "AL", "positive": 68, "negative": 28, "pending": null, "death": 0, "total": 96, "lastUpdateEt": "3/19 10:45", "checkTimeEt": "3/19 15:15" },{ "state": "AR", "positive": 46, "negative": 310, "pending": 113, "death": null, "total": 469, "lastUpdateEt": "3/19 11:23", "checkTimeEt": "3/19 15:16" },{ "state": "AS", "positive": 0, "negative": null, "pending": null, "death": 0, "total": 0, "lastUpdateEt": "3/14 00:00", "checkTimeEt": "3/19 16:18" },{ "state": "AZ", "positive": 44, "negative": 175, "pending": 130, "death": 0, "total": 349, "lastUpdateEt": "3/19 00:00", "checkTimeEt": "3/19 15:18" },{ "state": "CA", "positive": 924, "negative": 8787, "pending": null, "death": 18, "total": 9711, "lastUpdateEt": "3/19 14:25", "checkTimeEt": "3/19 15:20" } ...
So what could I do with this data? Why not bring it into a Domino database to start with, and then retrieve the data on a schedule, say every hour?
We should then be able to chart the data for each state over time. In order to not store the same data over and over again, I want to check if the data has been modified since the last time the agent ran. I will just use the lastUpdateEt date and time stamp in combination with the state abbreviation to perform a lookup. If I get any result(s) back, the data was already stored.
So how do you read the JSON from the API? In the past I would have used my own HTTP Request class, but this is not needed anymore. This is thanks to the NotesHTTPRequest class, first introduced in Domino 10 and then improved in Domino 11. In Domino 11 the wizards at HCL in Chelmsford added classes to parse JSON. The NotesJSONNavigator is the base of the parser, then you use NotesJSONArray, NotesJSONObject and NotesJSONElement to traverse through a JSON payload. When you get the hang of it, this is much easier than it maybe sounds at first.
So let’s take a look at my code. This is a scheduled agent, running once an hour:
%REM Agent Retrieve Data Created Mar 19, 2020 by Karl-Henry Martinsson/DBS %END REM Option Public Option Declare Sub Initialize Dim session As New NotesSession Dim db As NotesDatabase Dim view As NotesView Dim http As NotesHTTPRequest Dim json As NotesJSONNavigator Dim element As NotesJSONElement Dim stateArray As NotesJSONArray Dim state As NotesJSONObject Dim response As Variant Dim url As String Set db = session.CurrentDatabase Set view = db.GetView("LookupExisting") Call view.Refresh() Set http = session.CreateHTTPRequest() url = "https://covidtracking.com/api/states" response = http.get(url) Set json = session.CreateJSONNavigator(response) Set element = json.GetFirstElement() Do Until element Is Nothing Set state = element.Value Call processState(state, db, view) Set element = json.GetNextElement() Loop End Sub Function processState(state As NotesJSONObject, db As NotesDatabase, view As NotesView) Dim doc As NotesDocument Dim col As NotesViewEntryCollection Dim values List As String Dim element As NotesJSONElement Dim key As String Dim value As String Dim stateName As String Dim lastUpdate As String Set doc = New NotesDocument(db) doc.Form = "StateData" Set element = state.GetFirstElement() Do Until element Is Nothing key = element.Name value = element.Value If key="state" Then stateName = value End If If Right$(key,2)="Et" Then lastUpdate = Format$(CDat(value),"mm/dd/yyyy hh:nn AM/PM") Call doc.ReplaceItemValue("lastUpdated", lastUpdate) Else Call doc.ReplaceItemValue(key, value) End If Set element = state.GetNextElement() Loop Set col = view.GetAllEntriesByKey(stateName+"^"+lastUpdate) If col.count=0 Then Call doc.Save(True,False) End If End Function
This is all the code you need. Yes, I am serious. You can now consume any data on the web, served up by any system with a REST API, straight into Domino, with just a few lines for code.
The NotesHTTPRequest is very straight forward, so there is not much to say about it. But the classes used to parse JSON may need some explanation.
You start with the NotesJSONNavigator. You then use the value property of the NotesJSONElement class to get a value, an array or an object. The array or object is put into a NotesJSONArray or NotesJSONObject object, and you can then traverse down into the JSON structure. This is very powerful and useful, we have all been asking IBM for this functionality for many years. Now HCL delivers!
I created a hidden view for the lookup to avoid the same data stored multiple times. It only contains one (sorted) column, which is used by the lookup:
That is pretty much it.
I also created a view to display the data:
Soon we will do something more fun with the data. I will just let the scheduled agent run for a while and build up my database first. Keep your eyes open for the next post about this project!