Convert JSON data to Relational Format

DB2 11 for z/OS offers document storage support for JSON. The JSON data is stored internally as BSON (Binary-encoded Serialization of JSON) in inline BLOB column.

 

DB2 11 for z/OS provides the following SQL APIs to manipulate JSON data:

 

  • JSON_VAL built-in function: extract and retrieve JSON data into SQL data types from BSON.

 

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

 

  • SYSTOOLS.JSON2BSON UDF: Converts JSON document in string format to BSON format
  • SYSTOOLS.BSON2JSON UDF: Converts JSON document in BSON format to string format

 

Please refer to the following whitepaper for basic info about DB2 for z/OS SQL APIs:

http://www.ibm.com/developerworks/data/library/techarticle/dm-1501sql-json-db2/index.html

 

PI39003 removed the requirement that the first parameter of JSON_VAL function has to  be a column from a base table. It supports any expression that returns a BLOB value for the first argument.

Creating table to store JSON documents

If we want to store JSON data inside DB2, the data type of the column that are used to store JSON documents must be declared as an inline BLOB. It can be co-existed with other relational columns.

 

For simplicity, let assume there is one JSON document in BSON format stored in the DATA column of JSONPO table which is declared as BLOB datatype. Below is the content of that JSON document.  That is a purchase order with 2 items (in an array 'PO.items.item’)

 

 

{"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"

                       } 

                      ] 

                    } 

        }

}

SYSTOOLS.JSON_TABLE

 

This function returns array of elements in JSON data. This function is similar to XMLTABLE function. In short, it allows you to shred the JSON into relational format.

 

Below is the function prototype. It returns a table of two columns: type and value. Output column type contains the numeric representation of data type of JSON and output column value contains the value of the JSON field which is passed as 2nd argument of the function. 

The parameters of JSON_TABLE are same as that for JSON_VAL function.

  • the first parameter is the JSON document (in BSON format);
  • the second parameter is the JSON field you are interested in;
  • the third parameter is the intended SQL type of the returned JSON value.

 

CREATE FUNCTION SYSTOOLS.JSON_TABLE       

  ( INJSON         BLOB(16M)      

  , INELEM         VARCHAR(2048)  

  , RETTYPE        VARCHAR(100)   

  )                                        

  RETURNS TABLE                           

  ( TYPE  INTEGER        

  , VALUE VARCHAR(2048)                   

  )

 

 

Example 1:

Below is the SQL statement to find all the names of products, productName. From the syntax, it seems we are doing a JOIN, but in fact, we are not. In the following SQL statement, X is the result of the JSON_TABLE.

 

(Note: we use ! as SQL terminator in this article)

 

SELECT X.* FROM JSONPO, TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO.items.item.productName', 's:20')) X!

 

Output:

TYPE        VALUE 

2           Lawnmower 
2           Sapphire Bracelet

 

 

Example 2:

To find all the array entries:

 

SELECT X.* FROM JSONPO, TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO.items.item', 's:200')) X!

 

Output:

TYPE        VALUE

3           {@partNum:"872-AA",productName:"Lawnmower",quantity:1,USPrice:149.990000,shipDate:"2014-11-20"}
            
3           {@partNum:"945-ZG",productName:"Sapphire Bracelet",quantity:2,USPrice:178.990000,comment:"Not shipped"}

 

 

As you can see from above output, the VALUE output column contains the array entries as a JSON object.

 

Using JSON_TABLE with JSON_VAL function, you can easily convert data from JSON format to relational format.

 

Example 3:

SELECT

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), '@partNum', 's:10') as "@partNum",

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'productName', 's:20') as  "productName",

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'quantity', 'i') as "quantity",

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'USPrice', 'f') as "USPrice",

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'shipDate', 's:20') as "shipDate"

FROM JSONPO,

     TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO.items.item', 's:200')) X!

 

Output:

@partNum   productName        quantity    USPrice         shipDate            

872-AA     Lawnmower          1           149.99          2014-11-20          

945-ZG     Sapphire Bracelet  2           178.99          <null>              

  2 record(s) selected

 

From this example, the JSON_VAL function in the SELECT clause applies on the VALUE column of JSON_TABLE output.

 

Once we convert the JSON data to relational format, we can do whatever we can do for relational data. For examples, for users not familiar with JSON functions, we can create a view on the previous SQL statement. By doing, users can access JSON data from a view, just like other database objects.

 

Example 4:

CREATE VIEW ITEMVIEW(partNum, productName, quantity, USPrice, shipDate) AS

SELECT JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), '@partNum', 's:10'),

       JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'productName', 's:20'), 

       JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'quantity', 'i'),

       JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'USPrice', 'f'),

       JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'shipDate', 's:20')

FROM JSONPO,

     TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO.items.item', 's:200')) X!

 

SELECT PRODUCTNAME, SHIPDATE FROM ITEMVIEW!

 

Output:         

PRODUCTNAME          SHIPDATE

Lawnmower            2014-11-20          

Sapphire Bracelet    <null>

 

If you don’t want to store the whole JSON document inside DB2, you can first create a regular table to store the extracted/shredded output of JSON (i.e.  an inline BLOB for storing JSON is no longer needed). Then you can pass in the JSON document as a parameter marker as in following example.

 

Example 5:

CREATE TABLE T1 (

  PARTNUM VARCHAR(10),

  PRODUCTNAME VARCHAR(20),

  QUANTITY INT,

  USPRICE DECFLOAT,

  SHIPDATE VARCHAR(20))!

 

 

INSERT INTO T1

  SELECT JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), '@partNum', 's:10'),      

        JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'productName', 's:20') ,     

         JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'quantity', 'i'),       

         JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'USPrice', 'f') ,       

         JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'shipDate', 's:20')           

 FROM JSONPO,                                        

       TABLE(SYSTOOLS.JSON_TABLE(

                    SYSTOOLS.JSON2BSON(?), 'PO.items.item', 's:200')) X!

 

SELECT * from T1!

 

Output:

PARTNUM    PRODUCTNAME          QUANTITY    USPRICE  SHIPDATE 

872-AA     Lawnmower            1           149.99   2014-11-20  

945-ZG     Sapphire Bracelet    2           178.99   <null>

 

 

Summary

This article discussed how to convert JSON data into relational format using JSON enhancement in DB2 11 for z/OS that is delivered in PI39003.

Views: 1729

Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Bringing DB2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

PlanetDB2.com [Latest Blogs from the Biggest Names in DB2]

© 2017   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service