Creating Complex JSON Arrays in RPG

In order to provide data to more intricate UI elements in Ext JS such as trees or collapsible grids with nested lists, you will often find it necessary for your RPG programs to create JSON strings containing arrays within arrays.  While the Valence RPG Toolkit includes plenty of vvOut functions for sending JSON data that originates as an RPG variable, a data structure, a simple array, or an SQL select statement, more complicated scenarios may require a different approach.  This is where some popular open source JSON utilities available for RPG developers can offer a helping hand. Valence 5 includes wrapper functions to the RPG Next Gen JSON utilities, which can be accessed through VVSRVPGM via procedures documented in VVJSON and VVJSONA.  These procedures can be used to both create and consume JSON strings in RPG, but for today this blog post will focus on the creation side.  As an illustration of how the array-within-an-array concept works, we’ll use the VVJSON and VVJSONA procedures to create a JSON array of customers, with each customer containing a “subarray” of orders for each customer.  Schematically, we’re looking to create a JSON string that will reflect this:

• Customer A
  • Order A1
  • Order A2
  • Order A3
• Customer B
  • Order B1
  • Order B2
  • Order B3
• (etc)

The first step is to have our RPG program create a pointer that will be used to hold the giant JSON string containing our array with nested subarrays.  To do this we’ll use the vvJson_create() procedure, which when called returns a pointer to the memory that will ultimately be used to hold the JSON data built in subsequent VVJSON and VVJSONA calls.

ptrJsonString = vvJson_create();

Now we can start to assemble the JSON string.  Since we’re going to be building this JSON data one row at a time, we can either use native I/O (setll and read) or SQL (declare and fetch) to loop through the customer master to retrieve each of our customer master records. Prior to initiating the loop, we first want to define a separate memory pointer to hold the customer master array, for which we’ll use the vvJsonA_create() procedure.   Then, inside the loop, we’ll assemble the desired customer master record into a collection of JSON objects.  Each JSON object gets a dedicated memory space via another vvJson_create() call, which is then loaded with the desired customer master fields from the record using one of the various vvJson_put procedures, such as vvJson_putString(). Finally, we’ll add the assembled customer record object into the array using the vvJsonA_putObject() procedure. The RPG code to handle this process would look something like this:

ptrCustArray = vvJsonA_create(); // JSON array to hold the customers
setll *loval custMaster;
read custMaster;
dow not %eof;
  ptrCustRec = vvJson_create(); // JSON object to hold the customer record
  vvJson_putInt(ptrCustRec:'cusno':cusno);
  vvJson_putString(ptrCustRec:'cname':cname);
  vvJson_putString(ptrCustRec:'ccity':ccity);
  
  // (insert subarray of orders into ptrCustRec here -- see below)
  
  // add the customer master record to the JSON array, then move on to the next record
  vvJsonA_putObject(ptrCustArray:ptrCustRec);
  read custMaster;
enddo;

Inside the loop above, we want to create a mini-array of customer orders to be included as part of each customer record JSON object. To do this we’d just create yet another pointer to hold the order array, load it and finally add the array to the object using vvJson_putArray() procedure. The RPG code would look like this:

ptrOrderArray = vvJsonA_create(); // JSON array to hold the orders
setll cusno custOrders;
reade cusno custOrders;
dow not %eof;
  ptrOrder = vvJson_create(); // JSON object to hold the order record
  vvJson_putInt(ptrOrder:'orderNo':orderNo);
  vvJson_putString(ptrOrder:'item':item);
  vvJson_putDecimal(ptrOrder:'quantity':quantity);
  vvJson_putDecimal(ptrOrder:'price':price);
  
  // add order object to array, then get next order record
  vvJsonA_putObject(ptrOrderArray:ptrOrder);
  reade cusno custOrders;
enddo;

// place the orders array into the customer record
vvJson_putArray(ptrCustRec:ptrOrderArray);

Once the final customer record has been added, we can take the assembled array and put it into the pointer reserved for the whole JSON string, then pass the JSON on to the browser as follows:

vvJson_putArray(ptrJsonString:'customers':ptrCustArray);    
jsonString = %str(vvJson_toString(ptrJsonString));  // (jsonString defined as a very large varChar field)         
vvOut_data(*omit                                      
          :json                                       
          :%addr(jsonString:*data)                    
          :%len(jsonString));                         

Finally, we should use the vvJson_dispose() routine to clean up all the allocated memory. This can be done in one fell swoop by disposing of the ptrJsonString as follows:

vvJson_dispose(ptrJsonString)

If you’re on the latest Valence 5 build (release 5.0.20160716.0), then you can see an SQL-based example of this array-within-an-array concept in action by going into the Test RPG Call app and calling EXJSARRAY2 (no action required). This RPG program uses SQL fetch loops over the DEMOCMAST and DEMOORDER files to build out the JSON string. You can also see a practical example of these VVJSON concepts being applied in the Valence Examples app. The “Trees > Load All” example uses RPG program EXTREEALL to build a JSON array of states, with each state subsetted by the customers located there.  We’ll dive more into the process of building Ext JS trees in a future post. Image via James Jordan on flickr