Generating JSON output from your IBM i database with CoolSpools

JavaScript Object Notation (JSON) is a text-based format for storing and exchanging data that’s both human-readable and machine-parsable. It’s commonly used in web applications and API calls.

CoolSpools includes many conversion commands, such as CVTDBFCSV and CVTDBFXML, which convert from your IBM i database tables to CSV and XML formatted documents respectively, but there is no corresponding CoolSpools command to convert from your database tables to JSON. Does that mean that IBM i data cannot be converted to JSON format using CoolSpools? Well, not exactly…

If your server is at IBM i 7.2 or above then IBM SQL includes such functions as JSON_OBJECT and JSON_ARRAY to apply JSON formatting to your data. Running the CoolSpools command CVTDBFTXT with parameter FROMFILE(*SQL) or FROMFILE(*SQLSRC) allows you to convert the results of a SQL SELECT statement to a text document, so using this command in conjunction with the SQL JSON functions will allow you to generate a JSON formatted text document.

Example SQL Statement

As an example, our database includes an employee table that we want to extract as a JSON document. We can select the record set that we need for our JSON document using a simple SQL SELECT statement:

SELECT
   EMPID,
   FIRSTNAME,
   LASTNAME,
   TITLE,
   ROLE
FROM
   EMPLOYEE
ORDER BY
   EMPID

This will give us a record set with the required columns, as shown.

Now we can adjust our SQL SELECT statement to use the JSON_OBJECT function, which will apply JSON formatting to our record set. We need to specify each data element as a name/value pair.

SELECT JSON_OBJECT('Employee Id' VALUE EMPID,
                   'First Name' VALUE TRIM(FIRSTNAME),
                   'Last Name' VALUE TRIM(LASTNAME),
                   'Title' VALUE TRIM(TITLE),
                   'Role' VALUE TRIM(ROLE))
FROM
   EMPLOYEE
ORDER BY
   EMPID

Now our results are starting to look like JSON.

{"Employee Id":1002,"First Name":"Harold","Last Name":"Smith","Title":"Mr","Role":"IT Manager"}
{"Employee Id":2556,"First Name":"Valerie","Last Name":"Benoit","Title":"Dr","Role":"Managing Director"}
{"Employee Id":5789,"First Name":"Christine","Last Name":"Robinson","Title":"Ms","Role":"Systems Analyst"}
{"Employee Id":6788,"First Name":"Charles","Last Name":"Dubois","Title":"Mr","Role":"Developer"}
{"Employee Id":8122,"First Name":"Hayley","Last Name":"Brown","Title":"Ms","Role":"Head of Sales"}
{"Employee Id":9457,"First Name":"Francois","Last Name":"Blanc","Title":"Mr","Role":"Sales Rep"}

We can add more structure by nesting the JSON_OBJECT function as shown below:

SELECT JSON_OBJECT('Employee Id' VALUE EMPID,
                   'Employee Name' VALUE JSON_OBJECT('first' VALUE TRIM(FIRSTNAME),
                                                     'last' VALUE TRIM(LASTNAME)),
                   'Title' VALUE TRIM(TITLE),
                   'Role' VALUE TRIM(ROLE))
FROM
   EMPLOYEE
ORDER BY
   EMPID

Our JSON document now has additional structure around the employee name.

{"Employee Id":1002,"Employee Name":{"first":"Harold","last":"Smith"},"Title":"Mr","Role":"IT Manager"}
{"Employee Id":2556,"Employee Name":{"first":"Valerie","last":"Benoit"},"Title":"Dr","Role":"Managing Director"}
{"Employee Id":5789,"Employee Name":{"first":"Christine","last":"Robinson"},"Title":"Ms","Role":"Systems Analyst"}
{"Employee Id":6788,"Employee Name":{"first":"Charles","last":"Dubois"},"Title":"Mr","Role":"Developer"}
{"Employee Id":8122,"Employee Name":{"first":"Hayley","last":"Brown"},"Title":"Ms","Role":"Head of Sales"}
{"Employee Id":9457,"Employee Name":{"first":"Francois","last":"Blanc"},"Title":"Mr","Role":"Sales Rep"}

This is just an introduction to the JSON functions available in IBM i SQL. For further details and more complex examples see the link below:
https://www.ibm.com/docs/en/i/7.5?topic=data-generating-json

Example CoolSpools Command

Now that we have a working SQL statement, lets look at how we can incorporate that into a CoolSpools command. The first thing to note is that CoolSpools conversions will not support the JSON object data type, so you need to add a CAST() function to convert the JSON data into a character string.

SELECT CAST(JSON_OBJECT('Employee Id' VALUE EMPID,
                        'Employee Name' VALUE JSON_OBJECT('first' VALUE TRIM(FIRSTNAME),
                                                          'last' VALUE TRIM(LASTNAME)),
                        'Title' VALUE TRIM(TITLE),
                        'Role' VALUE TRIM(ROLE)) AS VARCHAR(32000))
FROM
   EMPLOYEE
ORDER BY
   EMPID

Now we have two options for running a conversion based on our final SQL SELECT statement.

• The first option is to use CVTDBFTXT FROMFILE(*SQL) and include the entire SQL string within the command parameters. This makes the command wholly self-contained, but can make the SQL statement a little difficult to read and maintain. The command string would be as shown below:

CVTDBFTXT FROMFILE(*SQL) TOSTMF(Employee_Data.json) STMFOPT(*REPLACE) SQL('SELECT CAST(JSON_OBJECT(''Employee Id''
       VALUE EMPID, ''Employee Name'' VALUE JSON_OBJECT(''first'' VALUE TRIM(FIRSTNAME),''last'' VALUE
       TRIM(LASTNAME)), ''Title'' VALUE TRIM(TITLE), ''Role'' VALUE TRIM(ROLE)) as VARCHAR(32000))
       FROM EMPLOYEE ORDER BY EMPID')

• The second option is to use CVTDBFTXT FROMFILE(*SQLSRC), placing the SQL SELECT statement into a source file member, and indicating the location of the source member within the CVTDBFTXT parameters. This makes the SQL a lot easier to read and maintain, so may be the better option when using the JSON functions.

CVTDBFTXT FROMFILE(*SQLSRC) TOSTMF(Employee_Data.json) STMFOPT(*REPLACE) SQLSRC(SRCLIB/SRCFIL EMPLOYEE)

Summary

In summary, while CoolSpools does not include specific commands to generate JSON formatted output, by utilising the JSON functions available in SQL since IBM i 7.2 you can still convert your IBM i data to a JSON document using CoolSpools commands, thanks to CoolSpools’ ability to use the results of any SQL SELECT statement as the source of a database conversion.