Last Updated on 9 October 2021 by Roberto De Pedrini
Whenever I have to create a JSON to call a Web Service or exchange data with other applications, 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 dispenser.
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
