TDIing out loud

Ramblings on the paradigm-shift that is TDI.

Wednesday, November 16, 2016

JSON and XML Tutorial - Part 4, JSON example


Practice examples are helpful when mastering new techniques. In this example we'll make a REST request and then handle the return JSON payload. We'll be using the service available at the following URL:

http://www.ideal-status.nl/static/consumer_notification_advice.json

If you dial up this URL you'll get JSON that looks like this:

{"0021":{"issuer_id":"0021","issuer_bic":"RABONL2U","issuer_name":"Rabobank","datetime":"2016-11-21 11:22:49","status":"OK","message":""},"0721":{"issuer_id":"0721","issuer_bic":"INGBNL2A","issuer_name":"ING Bank","datetime":"2016-11-21 11:23:05","status":"OK","message":""},"0031":{"issuer_id":"0031","issuer_bic":"ABNANL2A","issuer_name":"ABN Amro Bank","datetime":"2016-11-21 11:22:51","status":"OK","message":""},"0751":{"issuer_id":"0751","issuer_bic":"SNSBNL2A","issuer_name":"SNS Bank","datetime":"2016-11-21 11:23:07","status":"OK","message":""},"0811":{"issuer_id":"0811","issuer_bic":"BUNQNL2A","issuer_name":"Bunq","datetime":"2016-11-21 11:23:16","status":"OK","message":""},"0771":{"issuer_id":"0771","issuer_bic":"RBRBNL21","issuer_name":"RegioBank","datetime":"2016-11-21 11:23:12","status":"OK","message":""},"0761":{"issuer_id":"0761","issuer_bic":"ASNBNL21","issuer_name":"ASN Bank","datetime":"2016-11-21 11:23:10","status":"OK","message":""},"0091":{"issuer_id":"0091","issuer_bic":"FRBKNL2L","issuer_name":"Friesland Bank","datetime":"2016-11-21 11:22:58","status":"OK","message":""},"0511":{"issuer_id":"0511","issuer_bic":"TRIONL2U","issuer_name":"Triodos Bank","datetime":"2016-11-21 11:23:03","status":"OK","message":""},"0161":{"issuer_id":"0161","issuer_bic":"FVLBNL22","issuer_name":"Van Lanschot Bankiers","datetime":"2016-11-21 11:23:00","status":"OK","message":""},"0801":{"issuer_id":"0801","issuer_bic":"KNABNL2H","issuer_name":"Knab","datetime":"2016-11-21 11:23:14","status":"OK","message":""}}

In JSON terms, this represents a single object with a number of properties, each one with a four digit numeric name (e.g. '0021', '0721', ...). The values of these properties are in turn objects with their own properties like 'issuer_id' and 'issuer_name'. We will look at how to parse this notation into objects that we can leverage in an AssemblyLine.

To do this, create a new AL named jsonxmlTut4 and add the HTTP Client Connector in CallReply mode.

NOTE: CallReply is the most flexible mode for this component, allowing you to dynamically set any and all parameters via specially named attributes in the Output Map. For example, to specify a URL that overrides the parameter setting in the Connection tab, simply map out an attribute named 'http.url'. All parameters can be mapped in this way by prepending the Internal name of the parameter (which you get by pressing the pencil button
 next to a parameter) with the text 'http.'. And now back to the meat of this post...

Paste the URL above into the URL parameter of the Connector and use the 'map everything' wildcard (*) rule in the Input Map. Finally, add the DumpWorkEntry script in order to display what the HTTP Client Connector returns.


When you run the AL then you'll see output similar to this:



The prefix 'http.' is used by the HTTP Parser - which is an important part of the Connector - both for defining the request being sent, as well as for parsing response information. In the Attributes handled by the Parser you can find the standard HTTP headers like Content-Type, Content-Length, responseCode and responseMsg. You will also find special headers like Authorization and cookies (e.g. Set-Cookie). The service called here is also returning the ETag for the queried resource, which is like a fingerprint and/or version number for the resource.

In addition, there are three 'http.' attributes that represent the body of the response:
  • http.body which is the body of the HTTP response, presented according to the Content-Type setting. The above screenshot shows that this value is being returned as a byte array, although for some content types this attribute will have a String value.
  • http.bodyAsBytes always returns the HTTP body as a byte array.
  • http.bodyAsString always returns the HTTP body as a String.
Since we know the payload is a JSON string, we should be able to attach the JSON Parser to our HTTP Client Connector and get back the encoded data.




Now when we hit Run in console the AL, we get both the HTTP content plus a number of additional attributes. And note that even the HTTP content looks different in the log output now.


When reading, Parsers turn byte streams into Entries. Our wildcard (*) Input Map instructs that all attributes created in the conn Entry (the Connector's local cache) are copied into the work Entry. Typically when you see an Attribute displayed in dumpEntry() output it looks something like this:

        "attribute name": "attribute value"

If there are multiple values, then they are shown on separate lines and enclosed with square brackets. In the screenshot above you're seeing quoted attribute names at multiple layers, like "issuer_id" which appears in each of the parsed entities. Even the HTTP attributes appear without the 'http.' prefix and instead look to be contained in an object named 'http'.

This means that the JSON contained hierarchical data, so the Parser parses this into a hierarchical Entry object. Or, as in this case, it takes the flat work Entry holding the HTTP attributes returned by the Input Map of our Connector and adds the tree of parsed data.

Unfortunately, this gives us an Entry holding two types of data which can cause us grief. For example, if we wanted to convert the data to XML format by calling work.toXML() then we'll get an exception due to the mix of hierarchical and non-hierarchical data. For this reason, I never tie a parser to the HTTP Client Connector. Instead, I grab the value of the http.bodyAsString attribute and do the parsing myself:

  json = work.getString("http.bodyAsString");
  hEntry = work.fromJSON(json); // convert JSON to hierarchical Entry

If we replace the contents of the DumpWorkEntry Script with the above snippet then we get a cleaner hierarchical Entry (aka a true hEntry). An hEntry offers a number of handy features, including DOMInterface methods for traversing, searching and editing the tree, like .search(<xpath expressions>).getChildNodes().getElementsByTagName(<tag name>) and .addChildNode(). All these methods are detailed in the TDI JavaDocs for the Entry class. Just look for functions that return Node or NodeList, or that accept these as arguments.

So if we had a payload containing a series of nodes named 'Person' at whatever level in the hierarchy, we could retrieve the list of them like this:

  personList = work.getElementsByTagName("Person");
  for (node in personList) {
      task.logmsg(node.toString());
  }

The NodeList has only two methods: .getLength() and .item(<index>). But as you can see in the above example, TDI's Javascript engine lets you easily for-loop through the Nodes of a NodeList. You can also use square brackets to reference list members: personList[0].

And remember that this works for any hierarchical Entry, like that parsed from JSON or XML, as well as created by custom Parsers and script.

The Nodes returned by methods like .item() are both DOM Nodes and TDI Attributes, so it's wise to study the Javadocs for both. Since there are multiple types of Nodes - like those that represent XML node attributes and those that carry node values - it sometimes takes me a little trial and error to get my script right. And faithful readers will no doubt realize that I do this kind of data exploration either in the AL Debugger or in the Javascript tab at the bottom of the TDI CE.

However, sometimes instead of working with an hEntry, I just stick to Javascript. This means that I convert the JSON payload to a Javascript object and unwrap the data directly.

  json = work.getString("http.bodyAsString");
  jsobj = fromJson(json);
  for (prop in jsobj) { // return the name of each top-level property
      task.logmsg(prop + ": " + jsobj[prop]); // log prop name and value
  }

If I put this code into the DumpWorkEntry Script then the output looks like this:



So we see each of the entities in the JSON as a property with what looks like an identifier followed by '[object object]', which is the TDI JS engine's way of telling us that each value logged was another JS object. If we instead want to display the contents of each item we could either convert each back to JSON representation - toJson(jsobj[prop]) - or we could continue to explore the next level in the data tree. In fact, our logic could check if the value can be logged directly, or should be unwrapped:

  json = work.getString("http.bodyAsString");
  jsobj = fromJson(json);
  for (prop in jsobj) { // return the name of each top-level property
  subobj = jsobj[prop];
  if (typeof subobj == "object") { // explore next level
  task.logmsg(prop + ": ");
  for (subprop in subobj) {
  task.logmsg("  " + subprop + ": " 
+ subobj[subprop]);
  }
       } else {
  task.logmsg(prop + ": " + jsobj[prop]);
  }
  }

This time the output of our AL will look like this:


This is all well and good, we have a handle on the data now and could continue to do all our iterating in script. 

Sometimes however it's easier if we can loop through a data set using AL components. We can do this by scripting an Iterator to use in the built-in loop provided by the Feed section of the AL, or in a Connector Loop component. Or we could use a Conditional Loop and simply step through the entities with script. 

In either case we will want to convert the data into an entry object so that we can use it in Attribute Maps and other AL component features. And I usually leverage a hierarchical Entry when iterating data using components.

Let's start by using a Conditional Loop to do this. First we need to change the code in the DumpWorkEntry Script to this:

  json = work.getString("http.bodyAsString");
  hEntry = work.fromJSON(json);
  items = hEntry.getChildNodes(); // get top level NodeList
  index = 0; // initialize index

Now we can add the Conditional Loop calling it 'more items'. Press the Script button to add this scripted condition:


  return index < items.getLength();


Now we just have to remember to increment our index so we don't loop forever.
Finally, under the Loop we add another Empty Script with the following code:


  item = items[index]; // get next Node
  index++; // increment index

  work.removeAllAttributes(); // empty out work

  // set work attributes from Node values
  work.id = item.getName();
  children = item.getChildNodes(); // get next level
  for (child in children) {
  // set work attribute using child att name and value
       work[child.getName()] = child.getValue();
  }

  task.logmsg(work); // display this Entry

Our AL looks like this now:
And when we run the AL, we see the following output:


Now any components added under the Loop after our Set up work Entry script will have access to the work Entry holding data for a single entity from the parsed JSON payload.

And this post has gotten pretty long already. Look for follow-up post showing how to script a Connector to Iterate through the entities we just handled here.

9 comments:

Kishore VJ said...

Hi Eddie,

Thank you for the detailed explanation. We have a requirement to reconcile users from a cloud source where I get a JSON String received from httpclient connector using call reply mode.

When i tried the above approach through standalone TDI assembly line, I am able to iterate all the users with while conditional loop.

But, When i built the custom adapter to use this in reconciliation operation, the assembly line goes in to infinite loop and only creates the last user object derived from the while loop. Can you please help me with a approach how to build adapter for reconciliation purpose.

Thanks Again.

Kishore VJ said...

Hi Eddie,

We have a requirement to build adapter to reconcile users from JSON payload.

Using a standalone TDI AL, I am able to print all the user objects with your code. But, When i built the same in a custom adpater using ADT. The reconciliation process goes in to infinite loop while only creating the last user object from the while conditional loop.

Can you please help me if I am missing something. I have even tried using system.exitflow(). But still the reconciliaiton goes in to infinite loop.

Thanks,
Kishore

Eddie Hartman said...

@Kishore - Remember that for an adapter you have to send back data to ISIM (ISIG) with the success flag for each entry, as described in the RMI Adapter Development guide. E.g.

work.setProperty(Packages.com.ibm.di.dispatcher.Defs.STATUSCODE,
new Packages.java.lang.Integer (Packages.com.ibm.itim.remoteservices.provider.Status.SUCCESSFUL));

So you would replace the logmsg with logic to all the AL to exit instead. If system.exitFlow() is not working correctly, then maybe you need to restructure your AL so that it can be called multiple times, each time continuing where it left off (1 cycle runs).

Pieter Geerts said...

hi Eddie,
and what about complex/non-flat/hierarchical json data? work.fromJSON() seems to do a pretty bad job in comparison with other java json parsers, like gson or org.simple.
Especially handling single element arrays seems to be problematic.
I really would like to see IBM making progress with their parser.

Eddie Hartman said...

@Pieter You're right. You also have the built-in toJson() and fromJson() functions in the script engine, which are a bit more compliant. You're also right that there is work to be done here. Fingers crossed it happens soon :)

Pieter Geerts said...

oh didnt knew that, i was using com.ibm.json.java.JSONObject.parse to bypass the work.fromJson issues i was facing, but it seems that this fromJson() is probably mapped to that code ...
Thanx Eddie

M.Saqib Kiani said...

We need the same thing for our environment. Can you please send me the solution file(.xml)?

Eddie Hartman said...

@M.Saqib Kiani
Here is a link to the entire tdiingOL.xml config file. Use File > Import > TDI > Configuration file to read this into a new Project in your TDI setup and you'll have the jsonxmlTut4 AssemblyLine and more. Enjoy!

https://dl.dropboxusercontent.com/u/375185/TDIingOutLoud/tdiingOL.xml

M.Saqib Kiani said...

Thank you Eddie..!