We had a requirement to transform a result set returned by a database query into a JSON object, using ESQL, this article describes how we implemented this in IIB V10.

The input to this message flow was an xml message containing the user name and docket to be used in the database query for example:

  <DB2ToJSON>  <user_name>John</user_name>  <docket>356456</docket></DB2ToJSON>

The database table (DOCKET_TABLE) used in this example contained the following sample data:


This table was created in the DB2 SAMPLE database the JSON object we needed to create looked like this:

  {"UserName": "John""Docket": "356456""Entitlements": {          "Administration": {                    "AddUser": true                    "ApproveUser": false            }            "Deposit":{                    "Initiate": true            }            "Advances":{                    "Initiate": true                   "Approve": false                   "UpdateTxn": true                   "DeleteTxn": false                    "ViewTxn": true          }       }}

This example was implemented using IIB V10.0.0.8

The Message Flow

The message flow that we implemented to looked like this:



As can be seen from the screen shot the message flow used to implement the Database to JSON transformation was very simple. It consisted of one MQInput node - using the XMLNSC parser one ESQL compute node and two MQOutput nodes.

The ESQL to transform the database resultset to JSON.

The database access and the transformation work was done in the DatabaseToJSON ESQL compute node.
Firstly the ESQL retrieves the data from the database:

  SET Environment.Variables.Entitlements[] = SELECT T FROM Database.DOCKET_TABLE  AS T WHERE T.DOCKET_NUM = InputRoot.XMLNSC.DB2ToJSON_test.docket AND  T.USERNAME = InputRoot.XMLNSC.DB2ToJSON_test.user_name;    

This query uses the docket and user_name fields from the input message to query the database and places the resultset in Environment.Variables.Entitlements. Once the resultset was returned we used the following ESQL to transform it to JSON:

    SET OutputRoot.Properties.MessageType = 'JSON';  DECLARE CURRENT_CATEGORY CHAR '';  DECLARE entiltmentRef REFERENCE TO Environment.Variables.Entitlements;  CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'UserName' VALUE entiltmentRef.USERNAME;   CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE NameValue NAME 'Docket' VALUE entiltmentRef.DOCKET_NUM;   CREATE LASTCHILD OF OutputRoot.JSON.Data TYPE Name NAME 'Entitlements';    WHILE LASTMOVE(entiltmentRef)     DO   SET CURRENT_CATEGORY = TRIM(TRAILING FROM entiltmentRef.CATEGORY_NAME);       IF NOT(EXISTS(OutputRoot.JSON.Data.Entitlements.{CURRENT_CATEGORY}[])) THEN            CREATE LASTCHILD OF OutputRoot.JSON.Data.Entitlements TYPE Name NAME CURRENT_CATEGORY;       END IF;       IF entiltmentRef.ACTIVE = '1' THEN        CREATE LASTCHILD OF OutputRoot.JSON.Data.Entitlements.{CURRENT_CATEGORY} TYPE NameValue                    NAME TRIM(TRAILING FROM entiltmentRef.ENTITLEMENT_NAME) VALUE 'true';       ELSE        CREATE LASTCHILD OF OutputRoot.JSON.Data.Entitlements.{CURRENT_CATEGORY} TYPE NameValue                NAME TRIM(TRAILING FROM entiltmentRef.ENTITLEMENT_NAME) VALUE 'false';       END IF;       MOVE entiltmentRef NEXTSIBLING;  END WHILE;

This ESQL navigates the database resultset returned in Environment.Variables.Entitlements by using the reference 'entitlementRef' with the WHILE loop. Firstly the fixed part of the JSON objects UserName and Docket fields are created followed by the Entitlements element that will contain the list of entitlements.

There could be a varying number of entitlement categories the data base query returns the data sorted by category. The current category is stored in the CURRENT_CATEGORY local ESQL variable.