Skip to main content

JSON_TABLE Function

DB2 for i is the first DB2 Family member to add JSON_TABLE() to its SQL language support.

I’d like to thank John Eberhard and Scott Forstie for this blog article.  John is a member of the DB2 for i development team and published an article on the JSON_TABLE function late last year; this blog provides an overview of the JSON_TABLE function links to the full article.

There's a new type of data that can be consumed by using SQL on IBM i... JSON (JavaScript Object Notation).

DB2 for i is the first DB2 Family member to add JSON_TABLE() to its SQL language support.  Why did DB2 for i add JSON_TABLE() ? Because JSON is a pervasive data transport technology and like XML, customers can benefit from using a standards based, SQL Query Engine-centric solution.

Data is available in many formats.  Information available on the web can often be found in XML and JSON formats.   DB2 for i has had the ability to process data in XML format by using the XMLTABLE function for years.  With the most recent DB2 PTF Groups for IBM i 7.2 (SF99702 Level 14) and IBM i 7.3 (SF99703 Level 3), the JSON_TABLE function is added to DB2 for i.  JSON_TABLE is an SQL function that enables the easy decomposition of JSON data into relational data.

The power of JSON_TABLE can be easily utilized in combination with HTTP functions to access information from the web.  The following is a simple example of retrieving inflation information from the U.S. Bureau of Labor Statistics (bls.gov). This example utilizes a DB2 Global Variable for ease of demonstration.

Information about inflation from 2006 to 2015 can be obtain from the api.bls.gov website using the following SQL statements to create and set a variable to hold the result of a HTTP function request.

CREATE OR REPLACE VARIABLE INFLATION_INFO CLOB(1G) CCSID 1208 ;

SET INFLATION_INFO = systools.HTTPPOSTCLOB('http://api.bls.gov/publicAPI/v1/timeseries/data/', CAST ('<httpHeader> <header name="Content-Type" value="application/json"/> </httpHeader>' AS CLOB(1K)),CAST('{"seriesid":["CUUR0000AA0"], "startyear":"2006",  "endyear":"2015"}' AS CLOB(10K)));

Using JSON_TABLE, this information can be decomposed into relational information.

select * from JSON_TABLE(INFLATION_INFO  ,  '$.Results.series.data[*]'
     COLUMNS ("year" INTEGER,
                        "period" VARCHAR(5),
                         "value" double)
) x;

This query results in a table the looks like the following

year

period

value

2015

M12

708.524

2015

M11

710.952

2015

M10

712.458

2015

M09

712.777

2015

M08

713.89

....

 

 

 

 

 



This is only one example of how JSON_TABLE can be used.  Other examples can be found in the JSON_TABLE IBM developerWorks article found at  https://www.ibm.com/developerworks/ibmi/library/i-json-table-trs/index.html. 

The official documentation for the JSON_TABLE function can be found in IBM Knowledge Center at https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzscajsontable.htm.

HTTP Functions are explained in this White Paper: http://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/stg_ast_sys_wp_access_web_service_db2_i_udf

 
Webinars

Stay on top of all things tech!
View upcoming & on-demand webinars →