IBM i SQL JSON Create and Read

Lately, I have been researching JSON on the IBM i. Most articles deal with one side or the other of the process. I want to take you briefly through both sides. I have learned much at @RPGPGM.COM and cannot say enough good things about Simon. To make this easier for you to reproduce, I am using a standard sample data library; see Kent’s old article. If the link is broken, the important part is this:

• The following SQL statement creates a schema named KMSAMPLE and creates the SQL objects associated with the sample database in the newly created schema. This should be called from the ACS Run SQL Scripts window.

???????????????CALL QSYS.CREATE_SQL_SAMPLE('KMSAMPLE')

I prefer to set the CURRENT SCHEMA at the top of the script. Then, I do not need to qualify each file name. The data in the Employee file is a bit dated, so I bumped the dates by a few years.

set current schema KMSAMPLE; 

update EMPLOYEE set hireDate= hireDate+40 years, 
Birthdate = Birthdate+ 40 years  
with NC;         

This first SQL statement takes the details of the EMPLOYEE file and wraps them in a JSON object. I do not plan to use all these values in the next step, but this shows the basics for the file and the JSON object. I set the CLOB size to one mega-byte for now. This is the size for each JSON object from the data. CCSID 1208 is for the Unicode character set.

--https://www.rpgpgm.com/2023/08/creating-json-array-with-sql.html

set current schema KMSAMPLE;  

select workDept, json_object(
        key 'EMPNO' value empno, 
        key 'FIRSTNME' value firstnme,
        key 'MIDINIT' value midinit, 
        key 'LASTNAME' value lastname,
        key 'PHONENO' value phoneno,
        key 'HIREDATE' value hiredate, 
        key 'JOB' value job,
        key 'EDLEVEL' value edlevel, 
        key 'GENDER' value sex,
        key 'BIRTHDATE' value birthdate, 
        key 'SALARY' value salary,
        key 'BONUS' value bonus, 
        key 'COMM' value comm
        absent on null returning clob(1m) ccsid 1208 format json
      )
      from employee
      where workDept = 'A00'         

The end goal is to have a new SQL I can use combining the data from the department and the employee files so I can see employees per department. Yes, I could do this without JSON, but even IBM is storing JSON data in views. See the BASED_ON_FILES field in the newest version of SYSFILES.

Here is the SQL to pivot the data into a list of JSON objects into an array.

SELECT DEPTNO, DEPTNAME, JSON_OBJECT('Employees' 
       VALUE JSON_ARRAYAGG(
	   JSON_OBJECT(
  	     KEY 'EmpNo' VALUE empno,
	     KEY 'Name' VALUE CASE WHEN MIDINIT <> ' ' 
               THEN TRIM(lastname) || ', ' || FirstNme || ' ' || midinit 
                ELSE TRIM(lastname)   || ', ' || FirstNme END,
             KEY 'PhoneNo' VALUE phoneno, 
	     KEY 'HireDate' VALUE hiredate, 
	     KEY 'Job' VALUE job
       ABSENT ON NULL RETURNING CLOB(1M) CCSID 1208 FORMAT JSON )
        )
     ) EmpList
    FROM employee left outer join dept on workdept = deptno
    group by DEPTNO, DEPTNAME        

Now, we need to decide how to store this data so we can access it when we need it. Creating another file from this data in this format would work, but storing the data as a VIEW cuts down on the number of files and guarantees the data is up to date.

set current schema KMSAMPLE;    

drop view  Department_Employees_View;
 
CREATE OR REPLACE VIEW Department_Employees_View 
FOR SYSTEM NAME deptEmpV1
(DeptNo, DeptName, EmpList)  as 
SELECT DEPTNO, DEPTNAME, 
            JSON_OBJECT( 'Employees' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                KEY 'EmpNo' VALUE empno,
                KEY 'Name' VALUE
                CASE
                    WHEN MIDINIT <> ' ' THEN TRIM(lastname) || ', ' || FirstNme || ' ' || midinit
                    ELSE TRIM(lastname) || ', ' || FirstNme
                END,
                KEY 'PhoneNo' VALUE phoneno, 
                KEY 'HireDate' VALUE hiredate, 
                KEY 'Job' VALUE job
                ABSENT ON NULL RETURNING CLOB(1M) CCSID 1208 FORMAT JSON
            )
        )
    ) EmpList
    FROM employee left outer join dept on workdept = deptno
    group by DEPTNO, DEPTNAME;\
-- Object description 
LABEL ON TABLE  deptEmpV1 IS 'Departments with JSON Employee Data';
-- Column headings for the fields 
LABEL ON COLUMN deptEmpV1 (                     
DeptNo     IS 'Department Number',     
DeptName IS 'Department Name',     
EmpList      IS 'Employee JSON Object EmpNo, Full Name, Phone, Hire Date, Job'
); 
--Text descriptions for the fields 
LABEL ON COLUMN  deptEmpV1 (                     
DeptNo     TEXT IS 'Department Number',     
DeptName   TEXT is 'Department Name',     
EmpList    TEXT IS 'Employee JSON Object EmpNo,Name,Phone,...'
);              

This VIEW has both a long name and a system name. If we do not take control of the system name, we end up a logical file in KMSAMPLE named DEPAR000XX where XX is a number the system assigns based on how many other objects exist in the library with the same starting values. Better to have a plan and a pattern. Any time we create a file, we need to provide details for others who will be working with the objects. The column headings provide three lines of 20 characters, so pad spaces as needed to make it readable. Use a command like RUNQRY to easily see all values others will see.

RUNQRY QRYFILE((KMSAMPLE/DEPTEMPV1)) RCDSLT(*YES)

With the files and the view in place, we can now explode the JSON data along with the department data.

set current schema KMSAMPLE;     

select * from Department_Employees_View; 
----------------------------------  
  WITH DepList(Department,EmpList) 
  AS (
  SELECT DEPTNAME,EMPLIST
           from Department_Employees_View
         --where locate('SYSTEMS',Upper(DeptName)) <> 0
  )
  SELECT DepList.Department AS "Department",
         JSON.EmpNo AS "Employee Number",
         JSON.Name AS "Employee Name",
         JSON.HireDate AS "Hire Date"
    FROM DepList,
  JSON_TABLE(
    EmpList,
    '$.Employees[*]'
    COLUMNS(
      EmpNo varCHAR(10) PATH '$.EmpNo',
      Name varCHAR(50) PATH '$.Name',
      HireDate varchar(10) Path '$.HireDate'
      )
  ) AS JSON 
  order by DepList.Department, Json.Name;        

When we simply select all the data from the VIEW, we see the department number and description along with the JSON object for the Employees. Keep in mind, the Employees OBJECT contains an ARRAY of individual employee OBJECTS.

When run the SQL using JSON_TABLE, we can see all the employees for each department.

The WHERE statement is shown commented out above. When we run the SQL with the where statement active, we see only the departments with SYSTEMS in the description. Note the use of UPPER in the comparison. If the department name were mixed case, we would still get a hit.