Using an SQL interface to handle JSON data in Db2 for z/OS

This tutorial focuses on an SQL interface in Db2 for z/OS® that enables you to extract and retrieve JSON data from BSON objects and to convert from JSON to BSON. By using this interface, you can manage JSON data without relying on Db2 NoSQL JSON APIs. Instead, SQL interfaces can be used for JSON manipulation. Setup and configuration guidance is provided, as well as many examples for common JSON usage inside Db2 for z/OS. Hints and tips are provided to improve performance and prevent potential pitfalls.

Introduction

Db2 for z/OS supports document storage and manipulation for JSON documents. For documentation manipulation, it relies on Db2 NoSQL JSON APIs. JSON data is stored internally as BSON in an inline BLOB column. The conversion between JSON and BSON is handled by the Db2 NoSQL JSON APIs.

The original DB2 10 for z/OS JSON support introduced the JSON_VAL built-in function, which enabled you to extract and retrieve JSON data into SQL data types from BSON.

>>-JSON_VAL—(--json-value--,--search-string--,--result-type--)---------><

The JSON_VAL function returns an element of a JSON document that is identified by the JSON field name specified in search-string. The value of the JSON element is returned in the data type and length that is specified in result-type. For more information, see JSON_VAL.

The Db2 for z/OS JSON support also includes the following SQL interfaces:

These two UDFs, together with JSON_VAL, enable you to use SQL to do basic manipulation of JSON data. The invocation of these Db2 functions is referred to as SQL interfaces (or APIs) in this tutorial.

What is JSON?

JavaScript Object Notation (JSON) is a lightweight data format specified in IETF RFC 4627 and is based on a subset of the JavaScript programming language. JSON is a text format that is easy for humans to read and write, and easy for machines to parse and generate. With the increasing popularity of JavaScript and the simplicity of JSON itself, JSON has become popular for presenting information to JavaScript clients.

The following figure shows a simple example of a JSON document that illustrates a purchase order.

Figure 1. Simple example of a JSON document

{
  "PO": {
    "@id": 123,
    "@orderDate": "2013-11-18",
    "customer": { "@cid": 999 },
    "items": {
      "item": [
        {
          "@partNum": "872-AA",
          "productName": "Lawnmower",
          "quantity": 1,
          "USPrice": 149.99,
          "shipDate": "2013-11-20"
        },
        {
          "@partNum": "945-ZG",
          "productName": "Sapphire Bracelet",
          "quantity": 2,
          "USPrice": 178.99,
          "comment": "Not shipped"
        }
      ]
    }
  }
}

Setup and configuration

To enable JSON support in Db2 12 for z/OS you need the server-side UDFs for JSON document access, which are available in Db2 Accessories Suite for z/OS version 4.1 (FMID H2AS410).

To create the server-side UDFs, run the DDL in the Db2 Accessories Suite. A WLM environment must be configured for these UDFs.

Most of these UDFs are helpers that we don't normally use directly in an SQL statement, except SYSTOOLS.JSON2BSON and SYSTOOLS.BSON2JSON. The following two CREATE FUNCTION statements illustrate the definition of these UDFs.

Figure 2. Definition of SYSTOOLS.JSON2BSON

CREATE FUNCTION SYSTOOLS.JSON2BSON            
  ( INJSON                 CLOB(16M)          
  )                                           
  RETURNS                  BLOB(16M)          
  SPECIFIC JSON2BSON                          
  LANGUAGE C                                  
  PARAMETER STYLE SQL                         
  PARAMETER CCSID UNICODE                     
  NO SQL                                      
  WLM ENVIRONMENT DSNWLM_GENERAL              
  RUN OPTIONS 'XPLINK(ON)'                    
  PROGRAM TYPE SUB                            
  DETERMINISTIC                               
  DISALLOW PARALLEL                           
  DBINFO                                      
  RETURNS NULL ON NULL INPUT                  
  NO EXTERNAL ACTION                          
  EXTERNAL NAME 'DSN5JSJB';

Figure 3. Definition of SYSTOOLS.BSON2JSON

CREATE FUNCTION SYSTOOLS.BSON2JSON         
  ( INBSON                 BLOB(16M)       
  )                                        
  RETURNS                  CLOB(16M)       
  SPECIFIC BSON2JSON                       
  LANGUAGE C                               
  PARAMETER STYLE SQL                      
  PARAMETER CCSID UNICODE                  
  WLM ENVIRONMENT DSNWLM_GENERAL           
  RUN OPTIONS 'XPLINK(ON)'                 
  DBINFO                                   
  PROGRAM TYPE SUB                         
  DISALLOW PARALLEL                        
  NO SQL                                   
  DETERMINISTIC                            
  RETURNS NULL ON NULL INPUT               
  NO EXTERNAL ACTION                       
  EXTERNAL NAME 'DSN5JSBJ';

Note that JSON2BSON() and BSON2JSON() cannot apply on VARCHAR column.

The following sections show how to create a table to store, insert, select, and update JSON data.

Creating a table to store JSON documents

To use the SQL APIs to manipulate JSON documents, the data type of the column that is used to store JSON documents must be declared as an inline BLOB. The first two SQL statements in Figure 4 show how to create table to store JSON data. Although these tables use the same definition as the table implicitly created by JSON API, it is not required if you use only SQL APIs. By taking this approach, you have more flexibility in controlling the database objects to store and manipulate JSON data than the objects implicitly created by the JSON API. For example, you can create the table in a particular table space or database, and use a particular bufferpool; you can define an inline length other than 25000, BLOB column length other than 16M, etc.

Figure 4. Creating a table to store JSON data

CREATE TABLE  JSONPO( ID INTEGER NOT NULL,                     
                      DATA BLOB(16M) INLINE LENGTH 25000,      
                      PRIMARY KEY(ID)) CCSID UNICODE;   
                                 
CREATE TABLE  JSONCUSTOMER
                    ( ID INTEGER NOT NULL,                     
                      DATA BLOB(16M) INLINE LENGTH 25000,      
                      PRIMARY KEY(ID)) CCSID UNICODE;   
                                                                                           
CREATE TABLE  JSONPOTXT( DATA VARCHAR(5000)) CCSID UNICODE;

The JSONPO table will store the purchase order in binary format of JSON (BSON) in an inline BLOB column. The JSONCUSTOMER table will store customer information in binary format of JSON(BSON) in an inline BLOB column. The JSONPOTEXT table will store JSON format of purchase order in a VARCHAR column.

Inserting JSON documents

Db2 stores JSON documents in BSON format, so we need a function to convert JSON text to BSON format to insert JSON documents into Db2 tables. SYSTOOLS.JSON2BSON serves this purpose: it receives JSON data in text format and returns BSON format to the caller. The following example INSERT statement shows how to insert JSON data into the table by invoking the SYSTOOLS.JSON2BSON function.

Figure 5. Inserting data into a JSON document

INSERT INTO JSONPO VALUES (
 101,
 SYSTOOLS.JSON2BSON(
   '{"PO":{"@id": 101,                                       
           "@orderDate": "2014-11-18",                       
           "customer": {"@cid": 999},                        
           "items": {                                        
              "item": [{"@partNum": "872-AA",                         
                        "productName": "Lawnmower",                   
                        "quantity": 1,                                
                        "USPrice": 149.99,                            
                        "shipDate": "2014-11-20"                      
                       },                                              
                       {"@partNum": "945-ZG",                         
                        "productName": "Sapphire Bracelet",           
                        "quantity": 2,                                
                        "USPrice": 178.99,                            
                        "comment": "Not shipped"                      
                       }                                                
                      ]                                               
                    }                                                  
          }
    }')
);

INSERT INTO  JSONPO VALUES (
  102,
SYSTOOLS.JSON2BSON(
 '{"PO":{"@id": 102,                                       
           "@orderDate": "2014-12-20",                       
           "customer": {"@cid": 888},                        
           "items": {                                        
              "item": [{"@partNum": "872-AA",         
                        "productName": "Lawnmower",      
                        "quantity": 1,                     
                        "USPrice": 749.99,   
                        "shipDate": "2014-12-21"      
                       },                                       
                       {"@partNum": "837-CM",
                        "productName": "Digital Camera", 
                        "quantity": 2,
                        "USPrice": 199.99,
                        "comment": "2014-12-22"   
                       }                              
                      ]                                    
                   }                                                  
        }
  }'));             

INSERT INTO  JSONCUSTOMER VALUES (
   101,
   SYSTOOLS.JSON2BSON(
     '{"Customer":{"@cid": 999,                                        
                   "name": "Michael",                       
                   "age": 31,                                                               
                   "telephone": "234-343-2343",                                             
                   "country": "USA"                  
                   }
      }'));     
    
INSERT INTO  JSONCUSTOMER VALUES (
   102,
   SYSTOOLS.JSON2BSON(
      '{"Customer":{"@cid": 888,                                        
                    "name": "George",                       
                    "age": 29,                      
                    "telephone": "133-144-9999",       
                    "country": "USA"                  
                   }
      }'));

The following example INSERT statement illustrates the SQL statement to insert the JSON document into a VARCHAR column. Note that JSON2BSON() is not needed for this operation.

Figure 6. Inserting JSON documents into a VARCHAR column

INSERT INTO  JSONPOTXT VALUES (
   '{"PO":{"@id": 103,                                       
           "@orderDate": "2014-06-20",                       
           "customer": {"@cid": 888},                        
           "items": {                                        
             "item": [ { "@partNum": "872-AA",                         
                         "productName": "Lawnmower",                   
                         "quantity": 1,                                
                         "USPrice": 749.99,                            
                         "shipDate": "2014-06-21"                      
                       },                                              
                       { "@partNum": "837-CM",                         
                         "productName": "Digital Camera",           
                         "quantity": 2,                                
                         "USPrice": 199.99,                            
                         "comment": "2014-06-22"                     
                       }                                                
                     ]                                               
                   }                                                  
          }
    }');

Selecting whole JSON documents

If you select the JSON document directly from the column without using any functions, you'll see a JSON document in not very readable BSON format (because Db2 stores JSON as BSON internally as shown in the following figure).

Figure 7. Selecting JSON documents from a BLOB column without using BSON2JSON()

SELECT DATA FROM  JSONPO WHERE ID=101;
DATA
34a01000003504f004101000010406964006500000002406f7264657244617465000b00000032303134
2d31312d31380003637573746f6d6572000f000000104063696400e703000000036974656d7300f8000
000046974656d00ed0000000330006d0000000240706172744e756d00070000003837322d4141000270
726f647563744e616d65000a0000004c61776e6d6f77657200107175616e74697479000100000001555
350726963650048e17a14aebf6240027368697044617465000b000000323031342d31312d3230000003
3100750000000240706172744e756d00070000003934352d5a47000270726f647563744e616d6500120
00 
1 record(s) selected

To see the JSON document in a readable format, you need to invoke the SYSTOOLS.BSON2JSON function. This function receives the JSON data in a BSON format and returns the text format to the caller. So, by passing the column with JSON document to SYSTOOLS.BSON2JSON function, you'll get readable JSON data back. The following example SELECT statement illustrates how to use BSON2JSON() to retrieve the JSON document in text format.

Figure 8. Retrieving a JSON document

SELECT SYSTOOLS.BSON2JSON(DATA)       
FROM JSONPO;

Creating JSON index

To improve performance during SELECT, you can create an index on PO.customer.@cid in JSON, as shown in the following figure. First, write an expression using JSON_VAL to retrieve the PO.customer.@cid. Its desired return type is an integer, so i is specified. Additionally, you need to append :na to ensure that array type is not returned because Db2 does not support an array type yet. A composite index can also be created. You can write multiple JSON_VAL expressions in a single index to improve performance on various fields in a JSON document.

Figure 9. Creating a JSON index

CREATE INDEX IX1 ON JSONPO(
JSON_VAL(DATA, 'PO.customer.@cid','i:na'));

Selecting part of a JSON document

To retrieve the value of a specific field in a JSON document, you need to invoke a JSON_VAL function, which is a built-in function that provides an SQL interface to extract and retrieve JSON data into SQL data types from BSON objects. Its schema is SYSIBM. This function accepts only the BSON type of JSON document, so its argument needs to be a column from the table that contains the JSON document in BSON format or a SYSTOOLS.JSON2BON function that returns the BSON format of JSON document.

The following example SELECT statement shows how to invoke JSON_VAL to retrieve the value of JSON field in SQL data type. We're looking for the first productName under PO.items.item for PO.customers.@cid=999.

Figure 10. Using JSON_VAL

SELECT JSON_VAL(DATA, 'PO.items.item.0.productName', 's:10')     
FROM  JSONPO                                                
WHERE JSON_VAL(DATA,'PO.customer.@cid', 'i:na') = 999;

Expected output:

Lawnmower

A few key notes about the JSON_VAL function:

If you specify a path that does not exist, JSON_VAL returns a null value instead of an error message, as shown in Figure 11.
JSON_VAL will also return a null value if JSON data cannot be converted to the specified data type due to type incompatibility. In Figure 12, we want to convert PO.items.item.0.productName into integer, and a null value will be returned.
If :na is specified in the third argument of function invocation and an array is found, an error is returned.
If :na is not explicitly specified (by default), JSON_VAL will return the first element of the array, when an array is found.

Figure 11. Using JSON_VAL for non-existing path

SELECT JSON_VAL(DATA, 'PO.productName', 's:10') FROM  JSONPO;

Expected output:

<null>      
<null>      
  2 record(s) selected

Figure 12. Using JSON_VAL for incompatible data type

SELECT JSON_VAL(DATA, 'PO.items.item.0.productName', 'i')     
FROM  JSONPO;

Expected output:

<null>      
<null>      
  2 record(s) selected

The following table shows the supported result types of JSON_VAL:

Table 1. Supported JSON_VAL result types
Result type	Function return type/length	Notes
'n'	DECFLOAT(34)
'i'	INTEGER
'l'	BIGINT
'f'	DOUBLE
'd'	DATE
'ts'	TIMESTAMP
't'	TIME
's:n'	VARCHAR(n)	'n' means return bytes of the result data. Returns the null value if the result data is longer than 'n'. 'n' is an integer value in the range 1 - 32672.
'b:n'	VARCHAR(n) FOR BIT DATA	'n' means return bytes of the result data. Returns the null value if the result data is longer than 'n'. 'n' is an integer value in the range 1 - 32672
'u'	INTEGER / 4	Return 0 for elements whose values are explicitly set as NULL in the JSON document. If the value is set, but is NOT set to NULL, then return 1. For missing fields, this will return the null value.

Selecting data from joined JSON tables

We can query JSON data from multiple tables in a single SQL statement. In the following example SELECT statement, we're looking for all the customer names (Customer.name) from the JSONCUSTOMER table in which PO.customer.@cid in the JSONPO table is the same as Customer.@cid in JSONCUSTOMER.

Figure 13. Selecting JSON data from joined tables

SELECT JSON_VAL(T2.DATA, 'Customer.name', 's:20') as "Customer Name"              
FROM  JSONPO T1,  JSONCUSTOMER T2                 
WHERE JSON_VAL(T1.DATA, 'PO.customer.@cid', 'i') = JSON_VAL(T2.DATA, 'Customer.@cid', 'i');

Expected output:

Customer Name        
Michael              
George               
2 record(s) selected

Combining multiple JSON documents

You can combine two or more JSON documents into a single query by using the set operator. The following example SQL statement combines two JSON documents from different sources by using an UNION set operator. This example has a UNION operator with two legs: one leg contains JSON data from JSONPO table, and the other leg contains JSON data from the JSONPOTXT table. Because the JSONPOTXT table stores JSON in VARCHAR column, you need to use JSON2BSON to convert the textual format to binary format, then use BSON2JSON to convert to JSON format before UNION.

Figure 14. Performing a UNION on JSON documents

SELECT SYSTOOLS.BSON2JSON(DATA) FROM  JSONPO
UNION ALL
SELECT SYSTOOLS.BSON2JSON(
         SYSTOOLS.JSON2BSON((SELECT DATA FROM JSONPOTXT))
       ) AS DATA1
FROM SYSIBM.SYSDUMMY1;

Note that JSON2BSON() is used here to verify that DATA from the JSONPOTXT table is in a valid JSON format.

Sorting JSON documents

You can sort on JSON documents by invoking the JSON_VAL function in the ORDER BY clause. The following example SELECT statement returns JSON documents ordered (descending) by the PO.customer.@cid field.

Figure 15. Sorting a JSON document

SELECT SYSTOOLS.BSON2JSON(DATA)
FROM  JSONPO
ORDER BY JSON_VAL(DATA, 'PO.customer.@cid', 'i') DESC;

Updating JSON documents

You can update a JSON document in the table. The following example SQL statement updates a JSON document by invoking two functions: SYSTOOLS.JSON2BSON and JSON_VAL. In this example, JSON_VAL is invoked in the search condition to find the record to be updated ('Customer.@cid' =888), and SYSTOOLS.JSON2BSON is invoked to pass the new JSON data.

Figure 16. Updating a JSON document

UPDATE JSONCUSTOMER
SET DATA = SYSTOOLS.JSON2BSON(
              '{"Customer":{"@cid": 777,                                        
                "name": "George",                       
                "age": 29,      
                "telephone": "566-898-1111",
                "country": "USA"                  
               }}')
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 888;

Deleting JSON documents

Similar to the UPDATE operation on JSON documents, you can delete a JSON document from the table. The following example SQL statement deletes a JSON document. By invoking JSON_VAL in the search condition of the DELETE statement, you can locate the record to be deleted ('Customer.@cid' =777), and the DELETE statement deletes the JSON data if it's found.

Figure 17. Deleting a JSON document

DELETE  JSONCUSTOMER
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 777#