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_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.