Better examples for creating and consuming JSON

Whenever I am asked for an example of how to generate a JSON array or how to consume JSON data I have to point people to different posts, rather than have one that covers both. I have written this post to be that, examples of how to create and how to consume JSON.

I will be using Db2 for i to generate JSON, as I find it simpler to use than doing the equivalent in RPG.

I am going to give four examples in this post to show how to generate a simple JSON array, one with labels and the other without. Then consume the generated JSON into a format that can be easily processed by RPG. All of these examples are going to use embedded SQL in RPG programs.

I will be using my PERSON DDL Table for the input for these examples. What I want to do is to create a JSON containing:

1. Person identification number
2. What I call the "full name", which is: last_name, first_name
3. Date of birth

There are 41 rows in the PERSON Table. For illustrative purposes I am only going to show the first five in these examples, so not waste space in the post.

To list the first five rows in PERSON I can use the following SQL statement:

01 SELECT PERSON_ID, 02 LAST_NAME || ', ' || FIRST_NAME AS "Name", 03 DATE_OF_BIRTH 04 FROM PERSON 05 LIMIT 5

Line 1: Every person has a unique Person id. I used an identity column to generate the number.

Line 2: The double pipe symbol ( || ) is the equivalent of the Concatenate scalar function. Here I am concatenating the last name with a comma and the first name.

Line 3: Date of birth column.

Line 5: I am limiting my results to five for the reason I explained above.

The five results are:

PERSON DATE_OF _ID Name _BIRTH ------ --------------- ---------- 1 ALLEN, REG 1919-05-03 2 CROMPTON, JACK 1921-12-18 3 BYRNE, ROGER 1929-02-08 4 CAREY, JOHNNY 1919-02-23 5 MCNULTY, THOMAS 1929-12-30

I am going to need somewhere for the JSON output. I created two DDL Tables to contain the generated JSON:

01 CREATE TABLE MYLIB.OUTPUT1 (OUTPUT CLOB) ; 02 CREATE TABLE MYLIB.OUTPUT2 (OUTPUT CLOB) ;

Both contain a single CLOB column. CLOB stands for Character Large Object data type which can be used for storing large strings, of up to 2GB. This will be more than enough for the JSON I will be generating.

 

Create JSON with labels

I want to generate a JSON array where each element is labeled like:

{"IDENTIFIER":< person_id >,"NAME":< last_name + first_name >,"DATE_OF_BIRTH":< date_of_birth >}

The source code of the RPG program to format the data like this is:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 exec sql INSERT INTO OUTPUT1 05 SELECT JSON_OBJECT('ARRAY_1' 06 VALUE JSON_ARRAYAGG( 07 JSON_OBJECT('IDENTIFIER' : PERSON_ID, 08 'NAME' : LAST_NAME || ', ' || FIRST_NAME, 09 'DATE_OF_BIRTH' : DATE_OF_BIRTH))) 10 FROM PERSON ; 11 return ; 12 end-proc ;

Line 2: Using a Main procedure prevents the RPG cycle logic being included in this program. *SRCSTMT is my favorite control option, it makes the compiler use the source line number rather than generate its own source sequence numbers.

Line 3: Start of the Main procedure, which ends on line 12.

Line 4 – 10: The SQL statement to generate the JSON and insert it into the Table OUTPUT1.

Line 4: This is where I define that the results will be inserted into the Table OUTPUT1.

Line 5: The JSON_OBJECT denotes that I am creating a JSON object. The object must have a key name; I have called it "ARRAY_1".

Line 6: JSON_ARRAYAGG returns a JSON array containing an array element for each value in, this example, a set of JSON values.

Lines 7 – 9: JSON_OBJECT is used again to generate the JSON for the results returned from PERSON. I could have called anything I wanted, but chose to use the names of the columns in the Table.

Line 10: The data is found in the PERSON Table.

When this program is compiled, and called the JSON data is written the OUTPUT1 Table. I can view the result using the following:

01 SELECT * FROM OUTPUT1

The result is just one string:

OUTPUT --------------------------------------------------------------------------------------- {"ARRAY_1":[{"IDENTIFIER":1,"NAME":"ALLEN, REG","DATE_OF_BIRTH":"1919-05-03"},{"IDEN...

To make the result easier to understand I have taken the first five elements and will show them on separate lines below:

OUTPUT ------------------------------------------------------------------------------ {"ARRAY_1":[{"IDENTIFIER":1,"NAME":"ALLEN, REG","DATE_OF_BIRTH":"1919-05-03"}, {"IDENTIFIER":2,"NAME":"CROMPTON, JACK","DATE_OF_BIRTH":"1921-12-18"}, {"IDENTIFIER":3,"NAME":"BYRNE, ROGER","DATE_OF_BIRTH":"1929-02-08"}, {"IDENTIFIER":4,"NAME":"CAREY, JOHNNY","DATE_OF_BIRTH":"1919-02-23"}, {"IDENTIFIER":5,"NAME":"MCNULTY, THOMAS","DATE_OF_BIRTH":"1929-12-30"},

 

Consume JSON with labels

As I have this JSON data I need to create a program to consume it. I will not be inserting the data into a DDS file or DDL Table, I will be inserting it into a data structure array. I can then validate the information in the array before inserting the data into a file or Table.

My program is:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-ds DataIn qualified dim(*auto : 9999) ; 04 Id char(10) ; 05 Name char(30) ; 06 BirthDate char(10) ; 07 end-ds ; 08 dcl-proc Main ; 09 dcl-s Elements uns(5) inz(%elem(DataIn : *max)) ; 10 exec sql DECLARE C0 CURSOR FOR 11 SELECT ID,NAME,BIRTH_DATE 12 FROM OUTPUT1, 13 JSON_TABLE( 14 OUTPUT, 15 '$.ARRAY_1[*]' 16 COLUMNS( 17 ID DEC(10) PATH '$.IDENTIFIER', 18 NAME VARCHAR(30) PATH '$.NAME', 19 BIRTH_DATE CHAR(10) PATH '$.DATE_OF_BIRTH')) ; 20 exec sql OPEN C0 ; 21 exec sql FETCH C0 FOR :Elements ROWS INTO :DataIn ; 22 exec sql CLOSE C0 ; 23 return ; 24 end-proc ;

Lines 3 – 7: This is my data structure array. It is a modern array that will auto-extend its size depending upon the amount data that is inserted into it. It contains three subfields for the Person identifier, full name, and date of birth. I have defined the subfields as character as I cannot trust that the information coming from the JSON is in the data types I need to update my Table. The data structure was defined at the start of the program, to make it a global definition so that it might be used by an subprocedures in this program.

Line 8: Start of the Main procedure, that ends at line 24.

Line 9: This is the definition of a local variable, which is only available in the Main procedure. It will contain the value of the maximum number of array elements in DataIn.

Lines 10 – 19: This is a SQL cursor statement. I am using it to consume the JSON in the OUTPUT1 Table into columns that can then be fetched into the data structure array.

Line 10: Cursor is declared.

Line 11: The Select lists the columns I will be extracting from the JSON. Notice that I have given them different names to the labels in the JSON, just to show that this can be done.

Line 12: The data is in the Table OUTPUT1.

Line 13: JSON_TABLE return a result table from the JSON.

Line 14: The JSON data is in the column OUTPUT.

Line 15: "ARRAY_1" is the name of the JSON array.

Lines 16 – 19: The columns parameter is used to extract the JSON elements into table columns. The columns are identified by their labels.

Line 20: Open the cursor.

Line 21: Perform a multi-row fetch from the cursor, using the maximum number of array elements as the number of rows to fetch, into the data structure array DataIn.

Line 22: Close the cursor.

After compiling the program. I ran it with a debug breakpoint on the return, line 23. When I displayed the contents of the DataIn, the first five elements looked like:

> EVAL datain DATAIN.ID(1) = '1 ' DATAIN.NAME(1) = 'ALLEN, REG ' DATAIN.BIRTHDATE(1) = '1919-05-03' DATAIN.ID(2) = '2 ' DATAIN.NAME(2) = 'CROMPTON, JACK ' DATAIN.BIRTHDATE(2) = '1921-12-18' DATAIN.ID(3) = '3 ' DATAIN.NAME(3) = 'BYRNE, ROGER ' DATAIN.BIRTHDATE(3) = '1929-02-08' DATAIN.ID(4) = '4 ' DATAIN.NAME(4) = 'CAREY, JOHNNY ' DATAIN.BIRTHDATE(4) = '1919-02-23' DATAIN.ID(5) = '5 ' DATAIN.NAME(5) = 'MCNULTY, THOMAS ' DATAIN.BIRTHDATE(5) = '1929-12-30'

If this was in the "real world" I would write a subprocedure to validate this data, before inserting it into a Table or file.

 

Create JSON without labels

This time I do not want the labels in the JSON just the values, like:

[ < person_id >,< last_name + first_name >,< date_of_birth > ]

The program I wrote to achieve this was:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-proc Main ; 04 exec sql INSERT INTO OUTPUT2 05 SELECT JSON_OBJECT('ARRAY_2' 06 VALUE JSON_ARRAYAGG( 07 JSON_ARRAY(PERSON_ID, 08 LAST_NAME || ', ' || FIRST_NAME, 09 DATE_OF_BIRTH))) 10 FROM PERSON ; 11 return ; 12 end-proc ;

The differences from the equivalent program that had labels are:

Line 4: The JSON data will be inserted into the Table OUTPUT2.

Line 5: This JSON array is given the label "ARRAY_2".

Lines 7 - 9: To format the data from PERSON I use the JSON_ARRAY. No labels are given, just the columns of data I am using for the JSON array's values.

The JSON output from this program looks like:

The result is just one string:

OUTPUT ---------------------------------------------------- {"ARRAY_2":[[1,"ALLEN, REG","1919-05-03"],[2,"CRO...

To make the result easier to understand I have taken the first five elements and present them on separate lines:

OUTPUT ------------------------------------------------------------------------------ {"ARRAY_2":[[1,"ALLEN, REG","1919-05-03"], [2,"CROMPTON, JACK","1921-12-18"], [3,"BYRNE, ROGER","1929-02-08"], [4,"CAREY, JOHNNY","1919-02-23"], [5,"MCNULTY, THOMAS","1929-12-30"],

 

Consume JSON without labels

The program to copy the label-less JSON data into a data structure array is pretty much the same as the one I showed for the labels:

01 **free 02 ctl-opt main(Main) option(*srcstmt) ; 03 dcl-ds DataIn qualified dim(*auto : 9999) ; 04 Id char(10) ; 05 Name char(30) ; 06 BirthDate char(10) ; 07 end-ds ; 08 dcl-proc Main ; 09 dcl-s Elements uns(5) inz(%elem(DataIn : *max)) ; 10 exec sql DECLARE C0 CURSOR FOR 11 SELECT ID,NAME,BIRTH_DATE 12 FROM OUTPUT2, 13 JSON_TABLE( 14 OUTPUT, 15 '$.ARRAY_2[*]' 16 COLUMNS( 17 ID CHAR(10) PATH '$[0]', 18 NAME CHAR(30) PATH '$[1]', 19 BIRTH_DATE CHAR(10) PATH '$[2]')) ; 20 exec sql OPEN C0 ; 21 exec sql FETCH C0 FOR :Elements ROWS INTO :DataIn ; 22 exec sql CLOSE C0 ; 23 return ; 24 end-proc ;

The only differences are:

Lines 17 – 19: As the JSON data does not have labels the columns of data are identified by the element number in the array data.

The results are identical to the previous program; therefore, I am not going to waste the space to show them.

 

There I have it good examples of creating and consuming JSON data, all on one page, that I can share with the next person who asks me about creating and/or consuming JSON.

 

You can learn more about this from the IBM website:

• JSON_ARRAY
• JSON_ARRAYAGG
• JSON_OBJECT
• JSON_TABLE

 

This article was written for IBM i 7.6, and should work for some earlier releases too.