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: 6185

Add a Comment

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

Join The World of DB2

Comment by Jane Man on May 23, 2021 at 20:59

Hi Roger,

Thanks for reading my blog.

A quick look is : @id @orderDate is in parent level, which is NOT included in PO.items.item, so you cannot get @id @orderDate just from looking at  PO.items.item (unlike XMLTABLE function).

BTW, I have left Db2 development for almost 2 years, so I may be out-dated. Please open a ticket (PMR?) on this if you want a more accurate answer.

Hope this help.

Comment by Roger Smith on May 21, 2021 at 18:27

Hi Jane

 

Hi Jane – thank you for this blog post. I have followed the samples and worked all examples up to and Creating View.

My question is: Is it possible to Select the non-array and array value pairs at the same time?

 

I want to create a view that would show @id @orderDate customer   values and items.item values

 

If items.item omitted then array values are null

If included then @id values are null

 

SELECT

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

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

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

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

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'productName', 's:30') 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:30') as "shipDate"

FROM PDB2DBA.JSONPO,

     TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO.items.item', 's:500')) X;

 

orderdate @id  customer @partNum productName       quantity USPrice shipDate  

 --------- ---- -------- -------- ----------------- -------- ------- ----------

 NULL      NULL NULL     872-AA   Lawnmower                1  149.99 2014-11-20

 NULL      NULL NULL     945-ZG   Sapphire Bracelet        2  178.99 NULL     

 

 

 

SELECT

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

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

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

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

  JSON_VAL(SYSTOOLS.JSON2BSON(X.VALUE), 'productName', 's:30') 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:30') as "shipDate"

FROM PDB2DBA.JSONPO,

     TABLE(SYSTOOLS.JSON_TABLE(DATA, 'PO', 's:500')) X;    

 

orderdate  @id  customer @partNum productName quantity USPrice shipDate

 ---------- ---- -------- -------- ----------- -------- ------- --------

 2014-11-18 101  NULL     NULL     NULL            NULL    NULL NULL   

 2014-11-18 101  NULL     NULL     NULL            NULL    NULL NULL   

    

 

Latest Activity

William Shipley liked Surekha Parekh's blog post Live Db2 12 Client Panel 30th June 2021 - Sign Up today!
yesterday
A blog post by Surekha Parekh was featured
Tuesday
Daniel Luksetich posted a blog post
Monday
Gulfem Ogutgen liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Monday
Gulfem Ogutgen liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
edmundo nascimento liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
Michael Tiefenbacher posted an event
Thumbnail

30. DeDUG User Group Meeting at virtual

September 24, 2021 from 9am to 1:30pm
Monday
Earl Dugan posted an event

Virtual New England DB2 User's Group Thursday, June 24th, 2021. at Virtual Meeting

June 24, 2021 from 9am to 12pm
Monday
Willy Unger liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Saturday
carol goldberg liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
Adrian Collett liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
ONLO posted a discussion
Jun 16
GABRIEL FAJARDO liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 15
Pedro Diaz liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 14
An event by Surekha Parekh was featured
Thumbnail

Interactive Db2 12 Client Panel with John Campbell IBM DE at https://ibm.biz/Db212ClientPanel

June 29, 2021 from 11am to 12:30pm
Jun 14
Carol Davis-Mann posted a blog post
Jun 11

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

Forum

ADM10501W - Health indicator "Database Automatic Storage Utilization"

Started by ONLO in Application Development and DB2 Jun 16. 0 Replies

AIX Version 6.1DB2 10.5.6Hi every body,I got the above error:MESSAGE : ADM10501W Health indicator "Database Automatic Storage Utilization" ("db.auto_storage_util") breached the "upper" warning threshold of "80 %" with value "88 %" on "database"…Continue

DB2 Future

Started by Peter E Saxe in Application Development and DB2 May 20. 0 Replies

Has anyone ever submitted ideas of new functions in DBMS software like DB2?   have an idea for a future enhancement to DB2 that I would like to see.  Is this discussion area a good place to discuss this ?  Thanks.Continue

Tags: Enhancements, Future, DB2

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service