Last Updated on 9 October 2021 by Roberto De Pedrini
Whenever I have to create a JSON document for a Post-REST Web Service, I have to open previous programs and do a great job of copying and pasting …
I thought that creating a small source with the main things could help me … and why not create a Cheat-Sheet to share the IBM i Community?
So here’s a post and a Cheat-Sheet “DB2 for i SQL JSON_OBJECT essential cheat sheet” dedicated to JSON_OBJECT: you can download it, print it, and hang it on the wall behind your PC or at the coffee machine … (please let me know if you like the concept of Cheat-Sheets, I may spend some time preparing more … for web services, XML, SQL, RPG and so on).
The official IBM documentation about JSON generation with SQL is available at the following link “ Generating JSON Data “
Contributions from readers are welcome so as to enrich the collection with your examples … use the comments to add your contributions, I will edit the post to add examples, tricks, etc.
JSON_OBJECT – Creating a JSON from the DB or from variables and strings
The JSON_OBJECT scalar function allows the creation of JSON starting from the data of a table or view, from variables, or from strings and numbers.
Example 1 – Simple JSON from strings
-- Simple JSON
select json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value 1069.00)
from sysibm.sysdummy1;
--> Result
{
"id": 1,
"Brand": "Apple",
"Model": "Iphone 12 Pro",
"Price": 1069.0
}
-- Or, in a tableless way, for the same result
values(json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value 1069.00));
Example 2 – Simple JSON from a table (QIWS / QCUSTCDT is an example table we all have on IBM i)
-- Simple JSON from a Table
select json_object('CustomerId' value cusnum,
'LastName' value lstnam,
'Credit Limit' value CDTLMT)
from qiws.qcustcdt
where state='NY';
--> Result ... 3 json objects
{"CustomerId":839283,"LastName":"Jones ","Credit Limit":400}
{"CustomerId":397267,"LastName":"Tyron ","Credit Limit":1000}
{"CustomerId":192837,"LastName":"Lee ","Credit Limit":700}
Example 3 – Get a single JSON with multiple objects “inside”
select json_object('Credits' value json_arrayagg(json_object('CustomerId' value cusnum,
'LastName' value lstnam,
'Credit Limit' value CDTLMT) ))
from qiws.qcustcdt
where state='NY';
--> Result
{
"Credits": [
{
"CustomerId": 839283,
"LastName": "Jones ",
"Credit Limit": 400
},
{
"CustomerId": 397267,
"LastName": "Tyron ",
"Credit Limit": 1000
},
{
"CustomerId": 192837,
"LastName": "Lee ",
"Credit Limit": 700
}
]
}
Example 4 – JSON inside a JSON
select json_object('Credits' value json_arrayagg(json_object('CustomerId' value cusnum,
'Details' value json_object(
'LastName' value lstnam,
'Initials' value INIT,
'Street' value street,
'City' value city),
'Credit Limit' value CDTLMT)))
from qiws.qcustcdt
where state='NY';
--> Result
{
"Credits": [
{
"CustomerId": 839283,
"Details": {
"LastName": "Jones ",
"Initials": "B D",
"Street": "21B NW 135 St",
"City": "Clay "
},
"Credit Limit": 400
},
{
"CustomerId": 397267,
"Details": {
"LastName": "Tyron ",
"Initials": "W E",
"Street": "13 Myrtle Dr ",
"City": "Hector"
},
"Credit Limit": 1000
},
{
"CustomerId": 192837,
"Details": {
"LastName": "Lee ",
"Initials": "F L",
"Street": "5963 Oak St ",
"City": "Hector"
},
"Credit Limit": 700
}
]
}
Example 5 – Layered JSON using SQL’s Group By and JSON_ARRAYAGG functions
The group by function, together with JSON_ARRAYAGG will allow you to create arrays of JSON objects inside a JSON:
select json_object('state' value state,
'Count' value count(*),
'Details' value json_arrayagg(json_object('CustomerId' value cusnum,
'LastName' value lstnam,
'Credit Limit' value CDTLMT)
))
from qiws.qcustcdt
group by state
order by state;
--> Result
{"state":"CA","Count":1,"Details":[{"CustomerId":475938,"LastName":"Doe ","Credit Limit":700}]}
..
{"state":"NY","Count":3,"Details":[{"CustomerId":839283,"LastName":"Jones ","Credit Limit":400},{"CustomerId":397267,"LastName":"Tyron ","Credit Limit":1000},{"CustomerId":192837,"LastName":"Lee ","Credit Limit":700}]}
{"state":"TX","Count":2,"Details":[{"CustomerId":938472,"LastName":"Henning ","Credit Limit":5000},{"CustomerId":593029,"LastName":"Williams","Credit Limit":200}]}
..
Example 6: NULL management
-- For the null value ... I will use a Null Global variable
create variable faq400.GV_NULL char(1) default Null;
values(json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value faq400.gv_Null
));
--> Result (with the key price with a null value
{
"id": 1,
"Brand": "Apple",
"Model": "Iphone 12 Pro",
"Price": null
}
-- If we ad the ABSENT on NULL clause
values(json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value faq400.gv_Null
Absent on null
));
--> Result
{
"id": 1,
"Brand": "Apple",
"Model": "Iphone 12 Pro"
}
-- Another way to insert a null value
values(json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value cast(NULL as varchar(10))
));
-- Another one
values(json_object('id' value 1,
'Brand' value 'Apple',
'Model' value 'Iphone 12 Pro',
'Price' value 'null' format JSON
Null on null
));
Example No. 7 – Create a JSON document in the IFS
I talked about it in another post on this blog: “ DB2 for i & SQL – FAQ & Howto (Part.2) (IT) “, to the Faq” iDB2-FAQ-016 – How to store a CLOB in your IFS “… in this piece of ILE RPG code we see the use of a CLOB_FILE variable to store the result in an IFS file.
...
DCL-S OutFile SQLType(CLOB_FILE) CCSID(1208);
select json_object('Credits' value json_arrayagg(json_object(
'CustomerId' value cusnum,
'LastName' value lstnam,
'Credit Limit' value CDTLMT) ))
into :OutFile
from qiws.qcustcdt
where state='NY';
Outfile_Name = '/temp/accounts.json'
Outfile_NL = %len(%trimr(Outfile_Name))
Outfile_FO = SQFCRT;
Example No. 8 – Boolean values TRUE and FALSE in JSON
Boolean values (true) and (false) are written in JSON objects without the quotes (“) and always in lowercase … to distinguish them from alphanumeric values.
To obtain the value true and false we must use the non-specific “format JSON”, to indicate that the value of the last JSON is already a JSON!
-- These are Boolean
{"Boolean01":true}
{"Boolean02":false}
-- This is non a Boolean but a Alpha-Key
{"ThisIsNotABooleanKey":"true"}
{"ThisIsNotABooleanKey":TRUE}
-- Dealing with boolean in DB2 for i SQL and JSON_OBJECT
select json_object('Customers' value json_arrayagg(json_object('CustomerId' value cusnum,
'LastName' value lstnam,
'Is a creditor' value case when cdtdue>0 then 'true' else 'false' end format JSON)))
from qiws.qcustcdt
where state='NY';
--> Result
{
"Customers": [
{
"CustomerId": 839283,
"LastName": "Jones ",
"Is a creditor": false
},
{
"CustomerId": 397267,
"LastName": "Tyron ",
"Is a creditor": false
},
{
"CustomerId": 192837,
"LastName": "Lee ",
"Is a creditor": true
}
]
}
Example No. 9 – Creation of an HTTP Header for the QSYS2.HTTP functions
I talked about the QSYS2.HTTP functions to consume Web Service via SQL in the post “ New QSYS2.HTTP Functions SQL (7.4 TR5 and 7.3 TR11) “, I report below a part of RPG code where I use QSYS2.HTTP_POST to call web services of a payment system. Use of variables for URL, Header, Body, and Response to be able to log the call in a log table
..
dcl-s HttpHeader varchar(1024);
dcl-s Body varchar(10000);
dcl-s Url varchar(256);
dcl-s ResponseHeader varchar(1024);
dcl-s Responsemsg varchar(10000);
...
url='xx';
exec sql
set :body=Json_object(....)
exec sql
set :HTTPheader=
json_object('header' value 'content-type,application/json',
'header' value 'content-length,' concat length(trim(:BODY)),
'sslTolerate' value 'true');
// HTTP_POST
exec sql
select response_http_header, response_message
into :responseheader :Null_ds,
:responsemsg :Null_ds
from table(QSYS2.HTTP_POST_VERBOSE(:url,
cast (:body as clob(10M)),
cast (:HttpHeader as clob(3k)) ));
...
Example No. 10 – Json Object, Array and Array-Aggregate
select json_object('MyCompany' value json_arrayagg(json_object('id' value a.empno,
'Employee' value json_object('First Name' value a.FirstNme,
'Last Name' value a.Lastname,
'BirthDate' value a.Birthdate),
'paycheck' value json_array(json_object('Type' value 'Salary',
'Amount' value a.Salary),
json_object('Type' value 'Bonus',
'Amount' value a.Bonus),
json_object('Type' value 'Comm',
'Amount' value a.Comm)),
'workdept' value workdept,
'Collegues' value json_array( select trim(b.lastname) concat ' ' concat trim(b.FirstNme)
from sampledb.employee b
where b.workdept=a.workdept
and b.empno<>a.empno))))
from sampledb.employee a;
--> Result
{
"MyCompany": [
{
"id": "000010",
"Employee": {
"First Name": "CHRISTINE ",
"Last Name": "HAAS ",
"BirthDate": "1933-08-24"
},
"paycheck": [
{
"Type": "Salary",
"Amount": 52750.0
},
{
"Type": "Bonus",
"Amount": 1300.0
},
{
"Type": "Comm",
"Amount": 4220.0
}
],
"workdept": "A00",
"Collegues": [
"HAAS CHRISTINE",
"LUCCHESSI VINCENZO",
"O'CONNELL SEAN",
"HEMMINGER DIAN",
"ORLANDO GREG"
]
},
...
---
Roberto De Pedrini
Faq400.com
Beautiful! Thanks a bunch!