title: Dynamic JSON key/value pairs generation in ESQL
body: <p>How to transform JSON response retrieved from external system to meaningful data (key/value pairs) in ESQL?</p>
  
  <p><strong>Retrieved JSON:</strong></p>
  
  <pre><code>{
      "data": [
          {
              "name": "application.info.header",
              "value": "headerValue"
          },
          {
              "name": "entity.statistics.name.fullName",
              "value": "fullNameValue"
          },
          {
              "name": "application.info.matter",
              "value": "matterValue"
          },
          {
              "name": "entity.statistics.skill",
              "value": "skillValue"
          }
      ]
  }
  </code></pre>
  
  <p>where,</p>
  
  <ul>
  <li><p><code>name</code> ~ hierarchy of JSON (last attribute being the key)</p></li>
  <li><p><code>value</code> ~ value against the key</p></li>
  </ul>
  
  <p><strong>Expected JSON:</strong></p>
  
  <pre><code>{
      "data": {
          "application": {
              "info": {
                  "header": "headerValue",
                  "matter": "matterValue"
              }
          },
          "entity": {
              "statistics": {
                  "name": {
                      "fullName": "fullNameValue"
                  },
                  "skill": "skillValue"
              }
          }
      }
  }
  </code></pre>
  
  <p>Needless to say this can be easily achieved in Java through <code>Split</code> method - I'm looking for a suitable <a href="https://www.ibm.com/support/knowledgecenter/en/SSMKHH_10.0.0/com.ibm.etools.mft.doc/ak05190_.htm" rel="nofollow noreferrer">method</a> in ESQL.</p>
  
  <p><strong>Current ESQL Module:</strong></p>
  
  <pre><code>CREATE COMPUTE MODULE getDetails_prepareResponse
      CREATE FUNCTION Main() RETURNS BOOLEAN
      BEGIN
          DECLARE data REFERENCE TO InputRoot.JSON.Data.data.Item[1];
          SET OutputRoot.JSON.Data = InputRoot.JSON.Data;
          SET OutputRoot.JSON.Data.data = NULL;
  
          WHILE LASTMOVE(data) DO
              DECLARE keyA CHARACTER SUBSTRING(data.name BEFORE '.');
              DECLARE name CHARACTER SUBSTRING(data.name AFTER '.');
              DECLARE keyB CHARACTER SUBSTRING(name BEFORE '.');
              DECLARE key CHARACTER SUBSTRING(name AFTER '.');
              CREATE LASTCHILD OF OutputRoot.JSON.Data.data.{EVAL('keyA')}.{EVAL('keyB')}
              NAME key VALUE data.value;
              MOVE data NEXTSIBLING;
          END WHILE;
  
          RETURN TRUE;
      END;
  END MODULE;
  </code></pre>
  
  <p>This is currently handled through <code>SUBSTRING</code> method in ESQL (for 3 levels only), but now the JSON levels are dynamic (no limit to key/value pairs) as per requirements.</p>