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 linked JSON_TABLE IBM developerWorks article.

The official documentation for the JSON_TABLE function can be found in IBM Knowledge Center.

HTTP Functions are explained in this White Paper.

---