Creating JSON array with SQL

Last week I showed how I could retrieve information from a JSON array using SQL. This week I thought it would be a good thing to do the opposite: create a JSON array and insert it into a column in a SQL table.

To accomplish this I will be using several Db2 for i Table functions:

• JSON_ARRAY:  Creates a JSON array
• JSON_ARRAYAGG:  Creates a JSON array with an array element from each result row returned from a SQL statement
• JSON_OBJECT:  Create a JSON object (not a physical object like a file)

Creating the most basic JSON array is very simple. I just need to use the JSON_ARRAY table function and pass values to it:

01 VALUES (JSON_ARRAY('1','2','3'))

Which returns to me:

00001 -------------- ["1","2","3"]

What I do like about the JSON arrays is that I can give each element a name that allows someone else to know what information is contained within the element. Here I would use the JSON_OBJECT table function rather than the JSON_ARRAY. To name each element I could use the following:

01 VALUES (JSON_OBJECT(KEY 'ELEM_1' VALUE '1', 02 KEY 'ELEM_2' VALUE '2', 03 KEY 'ELEM_3' VALUE '3'))

The KEY allows me to name the element, and the VALUE provides the value. My results look like:

00001 ---------------------------------------- {"ELEM_1":"1","ELEM_2":"2","ELEM_3":"3"}

Rather than using the KEY and VALUE I can use the following, which has a colon ( : ) to separate the key from the value:

01 VALUES (JSON_OBJECT('ELEM_1' : '1', 02 'ELEM_2' : '2', 03 'ELEM_3' : '3'))

The results are the same as the previous statement.

If I wanted to give my array, or JSON object, a name I introduce the JSON_ARRAYAGG:

01 SELECT JSON_OBJECT('ARRAY_NAME' 02 VALUE JSON_ARRAYAGG( 03 JSON_OBJECT('ELEM_1' : '1', 04 'ELEM_2' : '2', 05 'ELEM_3' : '3'))) 06 FROM SYSIBM.SYSDUMMY1

Line 1: I need to use a Select statement, rather that a Values, as the JSON_ARRAYAGG effectively joins in this case two JSON_OBJECT Table function statements together. It is at this level I give the array, or JSON object, its name "ARRAY_NAME".

Line 2: The JSON_ARRAYAGG Table function. This is sorta-kinda used to join the two JSON_OBJECT together.

Lines 3 – 5: The same JSON_OBJECT statement that I showed earlier.

The results have the array name and the elements all named:

00001 --------------------------------------------------------- {"ARRAY_NAME":[{"ELEM_1":"1","ELEM_2":"2","ELEM_3":"3"}]}

Now onto a better example that takes data from a file and formats into a JSON array.

I have a file, PERSON, that contains the first name, city and state of residence.

01 SELECT * PERSON NAME CITY STATE ---------- --------------- ----- SIMON AUSTIN TX DOMINIC LOS ANGELES CA FRANCESCA LINCOLN NE JASON SALT LAKE CITY UT NOEL ST JOSEPH MO

I can modify the previous JSON SQL statement to create an array of this information:

01 SELECT JSON_OBJECT('INTERESTING_PEOPLE' 02 VALUE JSON_ARRAYAGG( 03 JSON_OBJECT('FIRST_NAME' : RTRIM(NAME), 04 'CITY' : RTRIM(CITY), 05 'STATE' : STATE))) 06 FROM PERSON

Line 1: I am calling this array "Interesting people".

lines 3 and 4: The fields in the file are fixed character. I am using the RTRIM scalar function to remove the trailing blanks.

My results are:

00001 --------------------------------------------------------------------------- {"INTERESTING_PEOPLE":[{"FIRST_NAME":"SIMON","CITY":"AUSTIN","STATE":"TX"}, {"FIRST_NAME":"DOMINIC","CITY":"LOS ANGELES","STATE":"CA"}, {"FIRST_NAME":"FRANCESCA","CITY":"LINCOLN","STATE":"NE"}, {"FIRST_NAME":"JASON","CITY":"SALT LAKE CITY","STATE":"UT"}, {"FIRST_NAME":"NOEL","CITY":"ST JOSEPH","STATE":"MO"}]}

To add another level of complexity I have another file, TELEPHONE, that contains the telephone numbers of these people:

01 SELECT * 02 FROM TELEPHONE 03 ORDER BY NAME NAME PHONE --------- ----- DOMINIC 3231234567 FRANCESCA 4021231234 FRANCESCA 4029876543 JASON 8011234567 SIMON 5121234567

Notice that Francesca has two records, and there is no data for Noel in the file.

I convert this to a simple JSON array by using the JSON_ARRAY:

01 SELECT JSON_ARRAY(RTRIM(NAME),PHONE) 02 FROM TELEPHONE 03 ORDER BY NAME

Which gives me:

00001 -------------------------- ["DOMINIC","3231234567"] ["FRANCESCA","4021231234"] ["FRANCESCA","4029876543"] ["JASON","8011234567"] ["SIMON","5121234567"]

I want to add another element to my Interesting people array for telephone numbers. The new element will list the telephone numbers found for the person. If the person has more than one phone number then a list of numbers needs to be returned. Let me show the SQL statement and then describe the parts to do with the new element:

01 SELECT JSON_OBJECT('INTERESTING_PEOPLE' VALUE 02 JSON_ARRAYAGG( 03 JSON_OBJECT('FIRST_NAME' : RTRIM(NAME), 04 'CITY' : RTRIM(CITY), 05 'STATE' : STATE, 06 'TELEPHONE' : JSON_ARRAY( 07 SELECT B.PHONE FROM TELEPHONE B WHERE B.NAME = A.NAME)) 08 )) 09 FROM PERSON A ;

Lines 1 – 5: Are the same as my previous SQL statement to make the JSON array.

Lines 6 and 7: The new element will be called "TELEPHONE" and I will populate it using the JSON_ARRAY. Within the JSON_ARRAY I have defined a SQL Select statement to retrieve the phone number from the Telephone file, where the name on the Telephone file is the same as that on the Person file. I have to prefix both columns used in the Where clause.

Line 9: Which is why the From for Person is followed by the letter "A".

The results are:

00001 -------------------------------------------------------------------------- {"INTERESTING_PEOPLE":[{"FIRST_NAME":"SIMON","CITY":"AUSTIN","STATE":"TX", "TELEPHONE":["5121234567"]}, {"FIRST_NAME":"DOMINIC","CITY":"LOS ANGELES","STATE":"CA", "TELEPHONE":["3231234567"]}, {"FIRST_NAME":"FRANCESCA","CITY":"LINCOLN","STATE":"NE", "TELEPHONE":["4021231234","4029876543"]}, {"FIRST_NAME":"JASON","CITY":"SALT LAKE CITY","STATE":"UT", "TELEPHONE":["8011234567"]}, {"FIRST_NAME":"NOEL","CITY":"ST JOSEPH","STATE":"MO", "TELEPHONE":[]}]}

Simon and Dominic have one phone number, Francesca has two, and Noel has a null element as there was no record in the file for him.

I want to take this JSON array and insert it into a column in a table. To do that I need a table, I am going to call OUTPUT. This is the SQL statement I used to created it:

01 CREATE TABLE MYLIB.OUTPUT ( 02 JSON_ARRAY 03 CLOB(65531) ALLOCATE(1000) 04 CCSID 1208 05 ) ;

Line 1: The table OUTPUT will be created in my library.

Lines 2 – 4: The table only has one column, JSON_ARRAY. I have defined it as a CLOB, Character Large OBject, with a length that is many more times that I need. I have used the ALLOCATE to only allocate a minimum of 1,000 characters in each row. And I decided to define that column as CCSID 1208, which is UTF8.

The SQL statement to insert the JSON array into the table is:

01 INSERT INTO OUTPUT (JSON_ARRAY) 02 SELECT JSON_OBJECT('INTERESTING_PEOPLE' VALUE JSON_ARRAYAGG( 03 JSON_OBJECT('FIRST_NAME' : RTRIM(NAME), 04 'CITY' : RTRIM(CITY), 05 'STATE' : STATE, 06 'TELEPHONE' : JSON_ARRAY(SELECT B.PHONE FROM TELEPHONE B WHERE B.NAME = A.NAME)))) 07 FROM PERSON A

Apart from Line 1 the statement is exactly the same as previous statement I showed to create the JSON array.

After the Insert has completed I can check the data that was inserted into the table:

01 SELECT * FROM OUTPUT

I am not going to show the results as it was exactly the same as the previous results.

I think you'll agree that the processes needed to created JSON arrays are fairly simple if we are using SQL.

 

• JSON_ARRAY SQL table function
• JSON_ARRAYAGG SQL table function
• JSON_OBJECT SQL table function

 

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