Construct complex nested JSON file based on a list of JSON path

What happened was my QAs did not want to change their mind to prepare data in Excel, using 'path' concept. They're too used to that method already since the XML time. They'd like to continue the same. Row contains Path and Cell/Column contains value. Each Cell/Column represents one test case. Sounds good?

I googled and googled and tried so many solutions suggested by a lot of kind men in StackOverflow, none of the solutions really gave me what I wanted. No one really did this before that building a complex/complicated nested JSON file. I decided to write the algorithm myself. 

How does the so called complex/nested look like? It looks like below. And I had to build the JSON from this list of paths. 

$.JSONDoc.OC.AL.data_type
$.JSONDoc.OC.AL.value
$.JSONDoc.OC.SIG.data_type
$.JSONDoc.OC.SIG.value
$.JSONDoc.IN.MCPL.PRODTYPE.data_type
$.JSONDoc.IN.USER.COUNTRYCODE.data_type
$.JSONDoc.IN.USER.COUNTRYCODE.value
$.JSONDoc.IN.MCPL.LOCATION.COUNTRY.data_type
$.JSONDoc.IN.MCPL.LOCATION.COUNTRY.value
$.JSONDoc.IN.USER.FRSCR.data_type
$.JSONDoc.IN.USER.FRSCR.value
$.JSONDoc.IN.MCPL.ITEMS.value[0].QUANTITY.data_type
$.JSONDoc.IN.MCPL.ITEMS.value[0].QUANTITY.value
$.JSONDoc.IN.CLU.SPLT180.data_type
$.JSONDoc.IN.CLU.SPLT180.value
$.JSONDoc.OUT.TXN.FRC.data_type
$.JSONDoc.OUT.TXN.FRC.value[0]

The algorithm below works well so far for any JSON path that consists of JSON Array as well. I know it's not perfect but I really hope it's helpful to you guys who've bumped into the similar issue that I had. One thing to note is that the 'has' method from JSONObject does not really give accurate result if the JSONObject has multi-level of nested JSONObject. Therefore I also wrote one for myself. 

public static JSONObject createJSONObject(String[] keys, int index, String value, JSONArray jArray, JSONObject masterJObj) {
        if (index < keys.length) {
            String key = keys[index];
            String nextKey = keys[index + 1];
            if (key.contains("[")) {
                return createJSONObject(keys, index + 1, value, jArray, masterJObj);
            }
            if ((index < keys.length - 2) && nextKey.contains("[")) {
                JSONArray jsonArray = new JSONArray();
                JSONObject jobj = new JSONObject();
                Object obj2 = getObject(masterJObj, key);
                if (obj2 != null) {
                    if (obj2 instanceof JSONObject) {
                        jobj = (JSONObject) obj2;
                        if (jobj.has(nextKey)) {
                            jsonArray = jobj.getJSONArray(nextKey);
                        }
                    }
                }
                createJSONObject(keys, index + 1, value, jsonArray, masterJObj);
                jobj.put(nextKey, jsonArray);
                return jobj;
            } else {
                JSONObject jsonObject1 = null;
                if (jArray != null) {
                    if (!jArray.isEmpty()) {
                        for (int j = 0; j < jArray.length(); j++) {
                            jsonObject1 = jArray.getJSONObject(j);
                            jArray = null;
                            break;
                        }
                    } else {
                        JSONObject jsonObject2 = new JSONObject();
                        jsonObject1 = new JSONObject();
                        jsonObject1.put(key, jsonObject2);
                        jArray.put(jsonObject1);
                    }
                } else {
                    Object obj1 = getObject(masterJObj, key);
                    if (obj1 != null) {
                        jsonObject1 = (JSONObject) obj1;
                    }
                }
                if (jsonObject1 == null) {
                    jsonObject1 = new JSONObject();
                }
                if (index == keys.length - 2) {
                    JSONObject jsonObject2;
                    if (jsonObject1.has(key)) {
                        jsonObject2 = jsonObject1.getJSONObject(key);
                    } else {
                        jsonObject2 = new JSONObject();
                    }
                    if (nextKey.contains("[")) {
                        nextKey = nextKey.substring(0, nextKey.indexOf("["));
                        JSONArray jArray1 = new JSONArray();;
                        if (!jsonObject1.isEmpty() && jsonObject1.has(nextKey)) {
                            jArray1 = jsonObject1.getJSONArray(nextKey);
                        }
                        jArray1.put(value);
                        jsonObject1.put(nextKey, jArray1);
                    } else {
                        if (!jsonObject1.isEmpty()) {
                            if (jsonObject1.has(key)) {
                                JSONObject jsonObject3 = jsonObject1.getJSONObject(key);
                                jsonObject3.put(nextKey, value);
                            } else {
                                jsonObject1.put(nextKey, value);
                            }
                        } else {
                            jsonObject2.put(nextKey, value);
                            jsonObject1.put(key, jsonObject2);
                        }
                    }
                    return jsonObject1;
                } else {
                    JSONObject returnedJObj = createJSONObject(keys, index + 1, value, jArray, masterJObj);
                    if (returnedJObj != null) {
                        if (returnedJObj.has(nextKey)) {
                            jsonObject1 = returnedJObj;
                        } else {
                            if (jsonObject1.has(nextKey)) {
                                if (!returnedJObj.isEmpty()) {
                                    String nextNextKey = returnedJObj.keys().next();
                                    Object jObj3 = returnedJObj.get(nextNextKey);
                                    if (jObj3 instanceof JSONObject) {
                                        jsonObject1.getJSONObject(nextKey).put(nextNextKey, jObj3);
                                    }
                                }
                            } else {
                                jsonObject1.put(nextKey, returnedJObj);
                            }
                        }
                    }
                }
                return jsonObject1;
            }
        }
        return null;
    }

    public static Object getObject(Object object, String searchedKey) {

        if (object instanceof JSONObject) {

            JSONObject jsonObject = (JSONObject) object;

            Iterator itr = jsonObject.keys();

            while (itr.hasNext()) {

                String key = (String) itr.next();

                if (!searchedKey.equals(key)) {

                    Object obj = getObject(jsonObject.get(key), searchedKey);

                    if (obj != null) {

                        return obj;

                    }

                } else {

                    return jsonObject.get(key);

                }

            }

        } else if (object instanceof JSONArray) {

            JSONArray jsonArray = (JSONArray) object;

            return getObjectFromJSONArray(jsonArray, searchedKey);

        }

        return null;

    }

Xpath Evaluation On XML where there are multiple Namespace Declaration and there's XMLNS Declaration Without Prefix

The XML contains multiple or more than one XMLNS namespace declaration. One of them is with declaration without prefix (http://www.ilog.com/rules/param) which causes issues where xpath can't read the Xml element.

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ds="http://www.ilog.com/rules/DecisionService" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<DecisionServiceResponse >
<DecisionID>RLOS001PL62000205DRAFT_2019-07-05-13:35:57:48957</DecisionID>
<underwritingRequest xmlns="http://www.ilog.com/rules/param">
<ns0:underwritingApprovalRequest xmlns:ns0="http://www.tmbbank.com/enterprise/model">
<application xmlns="">
<projectCode/>

I have a Java class that implements javax.xml.namespace.NamespaceContext which later will be instantiated and set in the setNamespaceContext of the class javax.xml.xpath.XPath. Below showing how I override the 3 methods from the NamespaceContext interface. 

    /**
     * This method is called by XPath. It returns the default namespace, if the
     * prefix is null or "".
     *
     * @param prefix to search for
     * @return uri
     */
    public String getNamespaceURI(String prefix) {
        if (prefix == null || prefix.equals(XMLConstants.DEFAULT_NS_PREFIX)) {
            return prefix2Uri.get(DEFAULT_NS);
        } else {
            return prefix2Uri.get(prefix);
        }
    }
    //This method is not needed in this context, but can be implemented in a similar way.
    public String getPrefix(String namespaceURI) {
        return uri2Prefix.get(namespaceURI);
    }
    public Iterator<String> getPrefixes(String namespaceURI) {
        // Not implemented
        return null;
    } 

Apart from the above, there is another method as below: 

    private void putInCache(String prefix, String uri) {
            prefix2Uri.put(prefix, uri);
            uri2Prefix.put(uri, prefix);
    }

    private void storeAttribute(Attr attribute) {
        // examine the attributes in namespace xmlns
        if (attribute.getNamespaceURI() != null
                && attribute.getNamespaceURI().equals(XMLConstants.XMLNS_ATTRIBUTE_NS_URI)) {
            // Default namespace xmlns="uri goes here"
            if (attribute.getNodeName().equals(XMLConstants.XMLNS_ATTRIBUTE)) {
                putInCache(DEFAULT_NS, attribute.getNodeValue());
            } else {
                // Here are the defined prefixes stored
                putInCache(attribute.getLocalName(), attribute.getNodeValue());
            }
        }
    }

The above method would not work because are multiple xmlns declarations, and prefix2Uri is a HashMap which does not allow duplicates, hence the prefix DEFAULT will be set to the most latest xmlns="" declaration than the one highlighted in yellow as above. 

Let me print something below for your clarity: 

ns0, http://www.tmbbank.com/enterprise/model
DEFAULT, http://www.ilog.com/rules/param
DEFAULT, ""

As you can see, eventually the prefix2Uri HashMap contains only 2 entries: 

ns0, http://www.tmbbank.com/enterprise/model
DEFAULT, ""

In order to fix this, just need to add one line in the putInCache method that looks like below, highlighted in light blue: 

    private void putInCache(String prefix, String uri) {
        if (prefix2Uri.get(prefix) == null || prefix2Uri.get(prefix).equals("")) {
            prefix2Uri.put(prefix, uri);
            uri2Prefix.put(uri, prefix);
        }
    }

Besides that, the XPath query that is used to query the XML, also needs to add DEFAULT as the prefix. 

Initially, the method that builds the XPath is as below: 

    public static String getPath(Node n) {
        StringBuilder path = new StringBuilder();
        do {           
            path.insert(0, n.getNodeName());
            path.insert(0, "/");
        } while ((n = n.getParentNode()) != null && n.getNodeType() == Node.ELEMENT_NODE);
        return path.toString();
    }

Now, it should look like the below , added lines highlighted in purple:

    public static String getPath(Node n) {
        StringBuilder path = new StringBuilder();
        do {           
            if(n.getNamespaceURI() != null && !n.getNamespaceURI().equals("") && (n.getPrefix() == null || n.getPrefix().equals(""))){                path.insert(0, UniversalNamespaceCache.DEFAULT_NS + ":" + n.getNodeName());
            } else {
                path.insert(0, n.getNodeName());
            }
            path.insert(0, "/");
        } while ((n = n.getParentNode()) != null && n.getNodeType() == Node.ELEMENT_NODE);
        return path.toString();
    }

Apache Camel DSL Sample That Does A Few Things

Below piece does a few things. It's useful if you are using Camel DSL like me. The explanation is in the form of comment below. 

<!-- Bean instantiation of Sql Server driver which will be referenced by below DSL -->

    <bean id="sqlServerDS"

        class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="${mssql.driverClassName}" />

        <property name="url" value="${mssql.url}" />

        <property name="username" value="${mssql.username}" />

        <property name="password" value="${mssql.password}" />

    </bean>

<!-- Setting up camel endpoint for XSLT which will be used to transform the received XML into another XML -->

<camel:endpoint id="someXSLT" uri="xslt:file://${xslt.location}/someXSLT.xslt"/>

<!-- Setting up File endpoint to output the generated output flat file -->

<camel:endpoint id="InformationFile" uri="file://?fileName=${output.location}/$simple{file:name}&amp;fileExist=Append"/>

<!-- Camel Route starts -->

<camel:route id="LalaLand">

                        <!-- Listening to someQueue which has been setup in ActiveMq -->

                        <camel:from uri="tcpActivemq:queue:someQueue"/>

                        <!-- transform the Xml picked up from someQueue using someXSLT -->

<camel:to ref="someXSLT"/>

                        <!-- define file name for the output file and stores it in Header -->

                        <camel:setHeader headerName="CamelFileName">

<camel:xpath resultType="java.lang.String">concat('somefile_', //code, '.txt')     </camel:xpath>

</camel:setHeader>

                        

                        <!-- logging -->

<camel:log message="SPLIT BEGINS" loggingLevel="DEBUG" logName="lalaland" />

                       <!-- Split the Xml transformed by someXSLT into lines -->

<camel:split>

<camel:xpath>//FileRecord</camel:xpath>

<camel:setHeader headerName="ctcRef">

<camel:xpath resultType="java.lang.String">//contactRef</camel:xpath>

</camel:setHeader>

                                <!- The payload will lose in the next section so needs to store the original message -->

<camel:setProperty propertyName="oriMessage">

<camel:simple>${body}</camel:simple>

</camel:setProperty>

                                <!-- if header variable ctcRef is not empty, Camel DSL will call 3 SQL to finally get the Date Of Birth, which is the requirement of this. Why 3 Sql? Because over here it does not support JOIN query -->

<camel:choice>

<camel:when>

<camel:simple>${header.ctcRef} != ''</camel:simple>

<camel:to uri="sql:SELECT ID FROM CONTACTS WHERE CONTACTREF = :#ctcRef;?dataSource=sqlServerDS" />

<camel:log message="body: ${body[0].get('ID')} " loggingLevel="DEBUG" logName="com.experian" />

<camel:setHeader headerName="contactId">

<camel:simple>${body[0].get('ID')}</camel:simple>

</camel:setHeader>

<camel:log message="contactId: ${header.contactId} " loggingLevel="DEBUG" logName="com.experian" />

<camel:to uri="sql:SELECT ACCOUNTID FROM ACCOUNTCONTACTS WHERE CONTACTTYPEID = 102 AND CONTACTID = :#contactId;?dataSource=sqlServerDS" />

<camel:setHeader headerName="accountId">

<camel:simple>${body[0].get('ACCOUNTID')}</camel:simple>

</camel:setHeader>

<camel:log message="accountId: ${header.accountId} " loggingLevel="DEBUG" logName="com.experian" />

<camel:to uri="sql:SELECT CONVERT(varchar,DATE_OF_BIRTH,23) AS DOB FROM CS_PERSON WHERE ACCOUNTS1 = :#accountId;?dataSource=sqlServerDS" />

                                                <!-- Store Date Of Birth in Header -->

<camel:setProperty propertyName="dob">

<camel:simple>${body[0].get('DOB')}</camel:simple>

</camel:setProperty>

<camel:log message="dob: ${header.dob} " loggingLevel="DEBUG" logName="com.experian" />

</camel:when>

</camel:choice>

                               <!-- Set the Body back with the original payload stored above -->

<camel:setBody>

<camel:simple>${property.oriMessage}</camel:simple>

</camel:setBody>

                                

                                <!-- Retrieves field value using xpath, add the dob from Header, and print in one line -->

<camel:transform>

<camel:xpath resultType="java.lang.String">concat(//accountRef, '|', //contactRef, '|', //title, '|', //givenName, '|', //middleName, '|', //familyName, '|', ${header.dob}, '&#xD;&#xA;')</camel:xpath>

</camel:transform>

                                

                               <!- The below will be executed after last line by checking based on CamelSplitComplete from Header -->

<camel:when>

<camel:simple>${header.CamelSplitComplete} == true</camel:simple>

                                        <!-- Below will add below wordings and number of records based on CamelSplitSize from Header to the output flat file -->

<camel:transform>

<camel:simple>adding new line here ${header.CamelSplitSize} </camel:simple>

</camel:transform>

</camel:when>

                                <!- content will be output to a physical file -->

<camel:to ref="someFile"/>

</camel:split>

</camel:route>

Ansible: Setting Dynamic Group in Add_Host Task using Jinja2 Templating

I'm very very excited! that I'm able to assign dynamic Group value programtically for the add_host task by using Jinja2 Templating in Ansible Playbook!

What happened was I was passing in a long string of different host-name with comma as the delimiter from Terraform in my "user_data". I needed to parse this long string and split the host-names into different group by matching with a few keywords.

For e.g. , hostnames="web01.xxx/com,web02.xxx.com,app01.yyy.com". My Ansible script will split this, loop it and assign the Group value.

Below is my sample code:

  - add_host:
      name: "{{item}}"
      group: >-
          {% set groupy = "default" -%}
          {% if 'web' in item  -%}
          {%   set groupy = "web" -%}
          {% elif 'app' in item -%}
          {%   set groupy = "app" -%}
          {% endif -%}
          {{ groupy }}
    with_items: "{{hostnames.split(',')}}"

Named Parameters for Apache Camel SQL (Spring DSL) that involves more than 1 table

I was working on this Apache Camel solution where I need to pick up XML from Apache MQ, then transform the XML payload into text. During the XML payload transformation, I have to enrich the content by querying the DB based on a field read from the same payload.

As in the official documentation version 2.12.4, named parameters can be used in the Endpoint URI , represented by the symbol :# .

However, in much recent versions, Apache Camel user can just directly access Header or Property in the Endpoint URI using :#${property.xxx} or :#{header.xxx}. But in my case, my Apache Camel version is 2.12.4, so I've to stick with the 2.12 way of doing things.

Anyway, the named parameters way works, but only to SQL query that only reads from 1 table. It will not work if the SQL query involves many tables such as joined query. I was getting error message "Invalid Column Name" , but it was never about the Column Name was wrong, it was about Camel SQL Component failed to recognize the :# symbol to replace the value.

So, what happened to the joined table query then? I just broke the SQL query into multiple pieces of Camel SQL calls, with each of them calling only one single table based on the value retrieved from the previous SQL call.

It's worth highlighting that the columns from result set will always be stored in Message Body as Java Map in an Java ArrayList. It looks like this upon printing on log [{ID=12345}]. 

Application Hung Caused by Corrupted Connection in DBCP Connection Pool

There was an incident where an unplanned network refresh causing the active connection object in the DBCP Connection Pool to lose the 'connection' to the corresponding database session and this resulted the application to hang upon getting connection from the pool.

This case was unique and was not reproducible due to no privilege to perform a network refresh intentionally. However I managed to reproduce this by intentionally killing the database sessions (Sql Server) while having SoapUI to fire the application continuously.

Solution
- via DBCP configuration. There are 2 solutions:

1. For Sql Server, it's fixed by setting "maxIdle" of the Apache DBCP as 0, the application always establishes a new connection upon new request.
2. For Oracle, what I observed from setting the "maxIdle=0" solution at local connecting to remote Oracle database was that the application was experiencing slowness upon getting connection from the DBCP pool. I did not want to compromise the application performance, therefore I tried another method which is to set the "maxWait" property, I set it at 500 milliseconds and at the same time had SOAPUI firing the application continuously. After killing the corresponding Oracle Connection Session, I noticed the application was able to create a new connection very soon, as opposed to previously the application would just be hung.

Tortoise Git SSH Auto-Authorization Issue

I'm using Tortoise Git as the Tortoise client to perform Git actions such as check modifications, clone, add, commit, push etc. via GUI. It's been a pain getting Tortoise to be able to be authorized automatically by the Git Serve.

Background

1. I've already had private key generated via MobaXTerm and stored in the Git machine's authorized_keys.
2. Now I want to enable the auto-authorization from TortoiseGit to the Git server upon performing actions such as Git Push. Therefore I need to let my Tortoise Git know the whereabout of the private key.
3. Here comes the problem, that specific private key has to be converted to Putty format. To do that, we use PuttyGen that is installed together with the Tortoise Git.
1. Conversations - Import Key (look for the specific Private Key)
2. Save private key to a location
4. Next, start Pageant which has also been installed by Tortoise Git installer. An icon will appear at the taskbar. Right click - > Add Key. Select the Private Key that you just created in previous step.