Wednesday, August 9, 2023

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.

 

 

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

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.