iCan Blog Archive

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

yearperiodvalue
2015M12708.524
2015M11710.952
2015M10712.458
2015M09712.777
2015M08713.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_74/db2/rbafzscajsontable.htm.

HTTP Functions are explained in this support document:
https://www.ibm.com/support/pages/new-http-functions-added-systools

This blog post was edited on October 29, 2020 to fix broken links.

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.

This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.