Most IBM i shops using webservices to talk to the Internet, will convert the data they are sharing into a web-service format like XML or JSON before sending it out. Obviously the responses will be in the same format and they need to convert it back into native IBM i format to use it. That conversion process has spawned lots of products to help (and occasionally hinder) the programmers responsible for creating these data conversions. Luckily there are some great $free, open-source products that will both encode XML/JSON and also decode it back into native IBM i data format. JSON_TABLE on IBM i is a new SQL function that will decode JSON without having to install any other product.

Lets look at JSON (which I prefer over XML) and a neat new way to READ JSON and CONVERT it back into a native IBM i File or SQL TABLE format. All dynamically.

I prefer an open source tool YAJL (Yet Another Json Library) to DEcode the JSON that I receive from webservices, and also use YAJL to ENcode native data into JSON format for outgoing webservices. But, since IBM have added a new function to IBM i SQL which will perform the DEcode of JSON->Data simply by using an SQL statement it merits some investigation:

JSON_TABLE on IBM i

IBM Says:

JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL. The main purpose of JSON_TABLE is to create a row of relational data for each object inside a JSON array and output JSON values from within that object as individual SQL column values. You can specify JSON_TABLE only in the FROM clause of a SELECT statement.

Which means:

JSON_TABLE is an SQL function that allow decoding of JSON formatted data (typically from a webservice call) and formats it in to a standard table/file format. This means from within IBM i programs we can use an SQL expression to read JSON DATA from a variable, the IFS or even connect to a webservice, receive the JSON and then return that JSON data in the form of an “IBM i Table containing Rows of Data”.

NOTE: JSON_TABLE cannot be used to ENcode JSON.. at this time.

Search to #JSON_TABLE in the blog and I will be adding some code examples.