What is JSON_TABLE on IBM i? 

 May 30, 2017

By  NickLitten


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:


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.


IBM i Software Developer, Digital Dad, AS400 Anarchist, RPG Modernizer, Shameless Trekkie, Belligerent Nerd, Englishman Abroad 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 remember: If at first you don't succeed then skydiving probably isn't a hobby you should look into.

Nick Litten

related posts:

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"cff50":{"name":"Main Accent","parent":-1},"a344d":{"name":"Accent Transparent","parent":"cff50"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"cff50":{"val":"var(--tcb-skin-color-0)"},"a344d":{"val":"rgba(46, 138, 229, 0.85)","hsl_parent_dependency":{"h":210,"l":0.54,"s":0.78}}},"gradients":[]},"original":{"colors":{"cff50":{"val":"rgb(0, 178, 255)","hsl":{"h":198,"s":1,"l":0.5}},"a344d":{"val":"rgba(0, 178, 255, 0.85)","hsl_parent_dependency":{"h":198,"s":1,"l":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__

Get In Touch

I’m always looking for awesome input, feedback and critique!