What is JSON_TABLE on IBM i?

IBM i

May 30

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.JSON_TABLE on IBM i

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.

 

SPOILER - JSON_TABLE is a cool function but its much slower at decoding JSON than the existing YAJL TOOL

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

 

RPG EXAMPLE READING JSON USING JSON_TABLE FROM IFS – https://www.nicklitten.com/rpg-example-reading-json-using-json_table-from-ifs/

RPG EXAMPLE READING JSON USING YAJL FROM IFS – https://www.nicklitten.com/rpg-example-reading-json-using-yajl-ifs/

Follow

About the Author

IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Alpha Nerd and Passionate Eater of Cheese and Biscuits. Nick Litten Dot Com is a mixture of blog posts that can be sometimes serious, frequently playful and probably down-right pointless all in the space of a day. Enjoy your stay, feel free to comment and in the words of the most interesting man in the world: Stay thirsty my friend.