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_TABLEcreates 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_TABLEis 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_TABLEonly in the
FROMclause of a
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.
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/
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.
Why use IBM i RDi?
Funky IBM i Email Validation Program using SQL Regex
How to Update IBM i ACS Access Client Solutions
#IBMi Java – Avoiding the QSH “Press ENTER to end terminal session” message
Windows Setup for IBM i Developers
Use RDi to make RPGLE lowercase
I remember the IBM AS400
Disable RPGLE ‘live parsing’ using Rational Developer (IBM RDi)
Latest RDi Fixpack for IBM Rational Developer (RPG Code Editor) is V18.104.22.168