JSON Automation

8 min readMar 6, 2021

maXbox Starter 82 — JSON in Code

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. A JSON Parser is then used to format the JSON data into a properly readable JSON Format with curly brackets. That can easily view and identify its key and values.

{ "date": "2021-3-4", "confirmed": 36223, "deaths": 1483, "recovered": 33632 }

Reading JSON data in maXbox could be easy. Json data can be read from a file or it could be a json web link. Let us first try to read the json from a web link.

Const JsonUrl = 'https://pomber.github.io/covid19/timeseries.json';

We use JSON for Delphi framework (json4delphi), it supports older versions of Delphi and Lazarus (6 or above) and is very versatile. Another advantage is the Object-pascal native code, using classes only TList, TStrings, TStringStream, TCollection and TStringList; The package contains 3 units: Jsons.pas, JsonsUtilsEx.pas and a project Testunit, available at: https://github.com/rilyu/json4delphi

Now we need a Load URL or Upload File function to get the json data for parsing. In our case load is a function-pair of open and send().

Let us first define the necessary packages “ msxml2.xmlhttp” and the JSON class itself:

var XMLhttp: OleVariant; // As Object Automation 
    ajt: TJson; JObj: TJsonObject2; 
XMLhttp:= CreateOleObject('msxml2.xmlhttp') 
XMLhttp.Open('GET', JsonUrl, False) //False is async XMLhttp.setRequestHeader('Content-Type','application/x-www-form-urlencoded'); 
XMLhttp.Send(); 
response:= XMLhttp.responseText; //assign the data 
statuscode:= XMLhttp.status;

Using async = false in Open() is not always recommended, but for a few small requests this can be ok. Remember that the script will NOT continue to execute, until the server response is ready. If the server is busy or slow, the application will hang or stop.
Anyway we open our XMLhttpObject (which is late binding) as not asynchronous, that is to say, synchronous because we need all data to continue:

Ref: RangeIndex: 76608 entries, 0 to 76607 Data columns (total 5 columns): # Column Non-Null Count Dtype 
--- ------ -------------- ----- 
country 76608 non-null object 1 
date 76608 non-null object 2 
confirmed 76608 non-null int64 3 
deaths 76608 non-null int64 4 
recovered 76608 non-null int64 dtypes: int64(3), object(2) memory usage: 2.9+ MB Worldwide Covid Deaths: 2517422 at the end of Feb. 2021

The send() method (XMLhttp.Send();) needs a further explanation: send() accepts an optional parameter which lets you specify the requests body; this is primarily used for requests such as PUT or POST. If the request method is GET or HEAD, the body parameter is ignored and the request body is set to null.
Im not sure if the content-type is the right, the MIME media type for JSON text is application/json and the default encoding is UTF-8. (Source: RFC 4627).
{content-type: application/json; charset=utf-8}
JSON is a SUB-TYPE of text but not text alone. Json is a text representation of an object (or array of objects). So I think both should be allowed. The question is which works better in practice and solution.
By the way if no Accept header has been set using the setRequestHeader(), an Accept header with the type “/ “ (any type) is sent.

Next we define the Json instance:

ajt:= TJson.create();

For slicing (filter) the data we copy the range from response timeseries.json:

startR:= pos('"'+ACOUNTRY1+'"',response); 
stopR:= pos('"'+ACOUNTRY2+'"',response); 
writeln('DataLen Overall: '+itoa(length(response))) 
resrange:= Copy(response, startR, stopR-startR); 
resrange:= '{'+resrange+'}'; //well formed 
//Now we parse the response. 
try 
  ajt.parse(resrange); 
except 
  writeln( 'Exception: <TJson>"" parse error: {'+
              exceptiontostring(exceptiontype, exceptionparam)) 
end;

Now we can iterate through the keys with values as items. Here, in the above sample JSON data: date, confirmed, deaths and recovered are known as key and “2020–1–22”, 0, 0 and 0 known as a Value. All Data are available in a Key and value pair.

First we get a list of all 192 country names as the node name:

JObj:= ajt.JsonObject; 
writeln('Get all Countries: ') 
for cnt:= 0 to jobj.count-1 do writeln(Jobj.items[cnt].name);...United Kingdom
Uruguay
Uzbekistan
Vanuatu
Venezuela
Vietnam...

So the country is an object to get. Ok, it is a JsonObject dictionary with 192 countries. Lets check the keys of our dict with a nested loop of all confirmed cases:

for cnt:= 0 to Jobj.count-1 do begin 
  Clabel:= Jobj.items[cnt].name; 
  JArray2:= jobj.values[Clabel].asArray; 
  for cnt2:= 0 to jarray2.count-1 do 
    itmp:= 
       jarray2.items[cnt2].asObject.values['confirmed'].asinteger; 
end;

So we pass the country name items[cnt].name from the Json Object in to the value list and we get back an a object array for the second iteration of at the moment 408 records with key values of integer format. Our dimension for now is 192 * 408 = 78336 records.

RangeIndex: 78336 entries, 0 to 78335 Data columns (total 5 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 
country 78336 non-null object 1 
date 78336 non-null object 2 
confirmed 78336 non-null int64 3 
deaths 78336 non-null int64 4 recovered 78336 non-null int64 dtypes: int64(3), object(2) memory usage: 3.0+ MB { "Afghanistan": [ { "date": "2020-1-22", "confirmed": 0, "deaths": 0, "recovered": 0 }, { "date": "2020-1-23", "confirmed": 0, "deaths": 0, "recovered": 0 },...

In a second attempt we visualize the timeseries with TeeChart Standard. Ok we got the objectarray as sort of dataframe with items and values but not in the form that we wanted. We will have to unwind the nested data like above to build a proper dataframe with chart series at runtime for TChart:

Chart1.Title.Text.clear; //AssignSeries(OldSeries,NewSeries:TChartSeries); Chart1.Title.Text.add('Sciplot Serie: '+'World Covid21 confirmed not †'); Chart1.Axes.Bottom.Title.Caption:= 'Days from '+ datetimetostr(date-
                                       400)+' to '+datetimetostr(date-1); 
Chart1.BottomAxis.Labels:= True; 
Chart1.LeftAxis.Logarithmic:= true; //Chart1.XValues.Multiplier:= 1 Clabel:=''; 
for cnt:= 0 to Jobj.count-1 do begin 
  Clabel:= Jobj.items[cnt].name 
  JArray2:= jobj.values[Clabel].asarray; 
  chart1.AddSeries(TFastLineSeries.Create(Self)); //runtime instances
  chart1.series[cnt].title:= Clabel; 
  TFastLineSeries(chart1.series[cnt]).LinePen.Width:= 4; 
  for cnt2:= jarray2.count-400 to jarray2.count-1 do begin 
     itmp:= 
          jarray2.items[cnt2].asObject.values['confirmed'].asinteger;
     sumup:= sumup+ itmp 
     chart1.Series[cnt].Addxy(cnt2,itmp, itoa(cnt2),clRed); 
  end; 
end; 
writeln('Worldwide Count:'+itoa(ajt.count)+' Covid Confirm: 
                                                 '+itoa(sumup));//*)

The plot you can find at:

TeeChart is a charting library for programmers, developed and managed by Steema Software of Girona, Catalonia, Spain. It is available as commercial and non-commercial software. TeeChart has been included in most Delphi and C++Builder products since 1997, and TeeChart Standard currently is part of Embarcadero RAD Studio 10.4 Sydney.

Conclusion: The proper way to use JSON is to specify types that must be compatible at runtime in order for your code to work correctly. 
The TJsonBase= class(TObject) and TJsonValue= class(TJsonBase) namespace contains all the entry points and the main types. 
The TJson= class(TJsonBase) namespace contains attributes and APIs for advanced scenarios and customization. Those are the supported types: 
type TJsonValueType =
           (jvNone,jvNull,jvString,jvNumber,jvBoolean,jvObject,jvArray); 
      TJsonStructType = (jsNone, jsArray, jsObject); TJsonNull = (null);
      TJsonEmpty = (empty);

https://github.com/rilyu/json4delphi/blob/master/src/Jsons.pas

JSON Parser Tools should have the following main functionality:
* Directly New your Record.
* Sava Temporary JSON Data.
* Copy and Paste JSON Data.
* Download JSON Data.
* Share Temporary Data anywhere.
* Load JSON URL directly into Editor.
* Redo and Undo facility when you edit your JSON online.
* JSON Validator for your Online Changes and your other JSON Data.
* Minify or Compact JSON Data to resave and reduct its Size.
* In Treeview, You can Search and highlight, and Sorting Data.
From: https://jsonparser.org/

Appendix for Python to get JSON Data:

import requests 
import pandas as pd data = requests.get('https://pomber.github.io/covid19/timeseries.json') 
jsondata = data.json() 
df = pd.DataFrame.from_dict(jsondata) df.info()

RangeIndex: 408 entries, 0 to 407
Columns: 192 entries, Afghanistan to Zimbabwe
dtypes: object(192)
memory usage: 612.1+ KB

columns=['country','date','confirmed','deaths','recovered'] 
data = [] 
for country in jsondata: for x in jsondata[country]:
   data.append([country,
                 ['date'],x['confirmed'],x['deaths'],x['recovered']]) 
df = pd.DataFrame(data,columns=columns)

Ref:
http://www.softwareschule.ch/examples/covid2.txt
https://github.com/rilyu/json4delphi
https://pomber.github.io/covid19/timeseries.json
http://www.softwareschule.ch/examples/unittests.txt
script: 1026_json_automation_refactor2.txt
Doc: https://maxbox4.wordpress.com

https://my6.code.blog/2021/03/03/json-automation/
https://entwickler-konferenz.de/speaker/max-kleiner/

Appendix Metrics

Most Called Report for *
C:\maXbox\mX47464\maxbox4\web\mX47520\Jsons.pas * 06/03/2021 14:15:40 *

These subprograms are called from two or more locations (62, was unknown):

15 Add Func, Method Jsons\TJsonObject (212)
13 Add Func, Method Jsons\TJsonArray (153)
13 CheckJsonObject Proc, Method Jsons\TJson (260)
11 CheckJsonArray Proc, Method Jsons\TJson (259)
10 Clear Proc, Method Jsons\TJsonValue (124)
9 Assign Proc, Method Jsons\TJsonValue (122)
9 GetCount Func, Method Jsons\TJsonObject (200)
8 GetCount Func, Method Jsons\TJsonArray (143)
8 RaiseError Proc, Method Jsons\TJsonBase (55)
6 Assign Proc, Method Jsons\TJsonArray (150)
6 Assign Proc, Method Jsons\TJsonObject (209)
6 Create Constructor Jsons\TJsonArray (145)
6 Create Constructor Jsons\TJsonObject (204)
6 RaiseAssignError Proc, Method Jsons\TJsonBase (57)
6 RaiseParseError Proc, Method Jsons\TJsonBase (56)
6 RaiseValueTypeError Proc, Method Jsons\TJsonValue (115)
5 Create Constructor Jsons\TJsonBase (59)
5 CreateArrayIfNone Proc, Method Jsons\TJson (252)
5 CreateObjectIfNone Proc, Method Jsons\TJson (253)
5 GetItems Func, Method Jsons\TJsonArray (142)
5 GetItems Func, Method Jsons\TJsonObject (201)
4 Clear Proc, Method Jsons\TJsonArray (167)
4 Clear Proc, Method Jsons\TJsonObject (231)
4 Delete Proc, Method Jsons\TJsonObject (228)
4 Destroy Destructor Jsons\TJsonBase (60)
4 SetAsNumber Proc, Method Jsons\TJsonValue (106)
3 Assign Proc, Method Jsons\TJsonPair (188)
3 Clear Proc, Method Jsons\TJson (274)
3 Create Constructor Jsons\TJsonValue (117)
3 GetAsArray Func, Method Jsons\TJsonValue (97)
3 GetAsObject Func, Method Jsons\TJsonValue (101)
3 GetIsEmpty Func, Method Jsons\TJsonValue (111)
3 GetOwner Func, Method Jsons\TJsonBase (51)
3 InternalStringify Proc, Method Jsons\TJsonBase (52)
3 Put Func, Method Jsons\TJsonArray (156)
3 Put Func, Method Jsons\TJsonArray (162)
3 Put Func, Method Jsons\TJsonObject (215)
3 Put Func, Method Jsons\TJsonObject (221)
3 SetAsBoolean Proc, Method Jsons\TJsonValue (104)
3 SetAsInteger Proc, Method Jsons\TJsonValue (105)
3 SetAsString Proc, Method Jsons\TJsonValue (107)
3 SetIsEmpty Proc, Method Jsons\TJsonValue (112)
3 SetIsNull Proc, Method Jsons\TJsonValue (108)
3 Split Proc, Method Jsons\TJsonBase (70)
2 Create Constructor Jsons\TJsonPair (183)
2 Decode Func, Method Jsons\TJsonBase (68)
2 Delete Proc, Method Jsons\TJsonArray (166)
2 Find Func, Method Jsons\TJsonObject (226)
2 GetAsBoolean Func, Method Jsons\TJsonValue (98)
2 GetOwnerName Func, Method Jsons\TJsonBase (54)
2 GetValues Func, Method Jsons\TJsonObject (202)
2 IsJsonArray Func, Method Jsons\TJsonBase (73)
2 IsJsonObject Func, Method Jsons\TJsonBase (72)
2 IsPairBegin Func, Local Jsons\TJsonBase\Split (577)
2 Parse Proc, Method Jsons\TJsonArray (148)
2 Parse Proc, Method Jsons\TJsonObject (207)
2 Parse Proc, Method Jsons\TJsonValue (120)
2 Put Func, Method Jsons\TJson (286)
2 Put Func, Method Jsons\TJson (298)
2 RaiseIfNone Proc, Method Jsons\TJson (255)
2 RaiseIfNotObject Proc, Method Jsons\TJson (257)
2 Stringify Func, Method Jsons\TJsonBase (63)

Unsupported types (7, was 0):

Items : Array (static) Property Jsons\TJsonArray (171)
Items : Array (static) Property Jsons\TJsonObject (235)

P : PChar ValParam Jsons\TJsonBase\Split\MoveToPair (592)
PtrBegin : PChar Var, Local Jsons\TJsonBase\Split (611)
PtrEnd : PChar Var, Local Jsons\TJsonBase\Split (611)

Values : Array (static) Property Jsons\TJsonObject (236)
Values : Array (static) Property Jsons\TJson (310)

Originally published at https://maxbox4.wordpress.com on March 6, 2021.

JSON Automation

maXbox Starter 82 — JSON in Code

Written by Max Kleiner