The JSON_TABLE clause in SQL DB2

This is an exercise to read a JSON strings stored a DB2 table field, parse out the data and store data in appropriate fields in existing DB2 table using a SQL statement JSON_TABLE clause

Step 1. Create DB2 table to hold parsed JSON data:

CREATE TABLE MYLIB.NEWCUST
(
   CUSTNUM       VARCHAR (100),
   CUSTNAME      VARCHAR (100),  
   ADDRRESS1     VARCHAR (100),
   ADDRRESS2     VARCHAR (100),
   PCODE         VARCHAR (100),
   CITY          VARCHAR (100),
   STATE         VARCHAR (100),
   ADD_DATE      DATE
)

Step 2. Create a DB2 table with one big CLOB field to hold parsed JSON data

CREATE TABLE MYLIB.JDATA_IN (JDATA CLOB (1G) CCSID 1208 )

Step 3. Populate JDATA_IN with some test data. Note that data we want is contained under "customer" node.

INSERT INTO MYLIB.JDATA_IN VALUES( CAST('
{
   "order_number":2,
   "customer":[
      {
	     "custnum":"12345678",
         "custname":"Janice Zoot",
         "addrress1":"123 Main Street",
         "addrress2":"",    
         "pcode":"12345",       
         "city":"Anaheim",       
         "state":"CA",     
         "add_date": "2018-01-01-12.22.01.01"
      },      
      {
	     "custnum":"22345678",
         "custname":"Floyd Pepper",
         "addrress1":"555 Elm Street",
         "addrress2":"",    
         "pcode":"12346",       
         "city":"Anaheim",       
         "state":"CA",     
         "add_date": "2018-01-01-12.22.01.01"
      }
   ]
}
' AS CLOB))

Step 4. Parse out data from JSON string into appropriate fields in table MYTABLE. Here JSON_TABLE targets data in the customer nodes.

INSERT INTO MYLIB.NEWCUST (CUSTNUM, CUSTNAME, ADD_DATE)
   SELECT CUSTNUM, CUSTNAME, ADD_DATE
    FROM MYLIB.JDATA_IN, 
    JSON_TABLE (JDATA,'$.customer[*]'
    COLUMNS (
         custnum  varchar (100) PATH '$.custnum'  ,
         custname varchar (100) PATH '$.custname' ,
         add_date varchar (100) PATH '$.add_date'
    )
)

Step 5. Make sure it worked OK

SELECT * FROM  MYLIB.NEWCUST