
Last Updated on 9 Ottobre 2021 by Roberto De Pedrini
Ogni volta che devo creare un JSON per chiamare un Web Service o scambiare dei dati con altre applicazioni, devo andare a prendere programmi precedenti e fare un gran lavoro di copia e incolla … Ho pensato che crearsi un piccolo sorgente con le cose principali poteva essermi di aiuto… per documentarlo meglio l’ho poi trasformato in Cheat-Sheet … allora perché non condividerlo con la Community IBM i?
Ecco quindi un post e un Cheat-Sheet “DB2 for i SQL JSON_OBJECT essential cheat sheet” dedicato al JSON_OBJECT : potete scaricarlo, stamparlo e appenderlo al muro dietro il vostro PC o al distributore del caffè.
La documentazione ufficiale IBM riguardo alla generazione di JSON con SQL e disponibile al seguente link “Generating JSON Data“
Sono graditi contributi da parte dei lettori così da arricchire la raccolta con i vostri esempi… utilizzate i commenti per aggiungere i vostri contributi, modificherò il post per aggiungere esempi, tricks ecc.
JSON_OBJECT – Creazione di un JSON dal DB o da variabili e stringhe
La funzione scalare JSON_OBJECT permette la creazione di JSON partendo dai dati di una tabella o view, da variabili oppure da stringhe e numeri.
Esempio 1 – Semplice JSON da stringhe
-- 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));
Esempio 2 – Semplice JSON da una tabella (QIWS/QCUSTCDT è una tabella di esempio che abbiamo tutti su 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}
Esempio 3 – Ottenere un unico JSON con più oggetti “dentro”
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
}
]
}
Esempio 4 – JSON dentro un 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
}
]
}
Esempio 5 – JSON a più livelli utilizzando le funzioni di Group By e JSON_ARRAYAGG di SQL
La funzione group by, insieme a JSON_ARRAYAGG permetter di creaere array di oggetti JSON dentro un 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}]}
..
Esempio 6: Gestione dei NULL
-- 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
));
Esempio Nr. 7 – Creare un JSON document nell’IFS
Ne ho parlato in un altro post di questo blog: “DB2 for i & SQL – FAQ & Howto (Part.2) (IT)“, alla Faq “iDB2-FAQ-016 – How to store a CLOB in your IFS” … in questo spezzone di codice ILE RPG vediamo l’uso di un variable CLOB_FILE per memorizzare il risultato in un file IFS.
...
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;
Esempio Nr. 8 – Valori Booleani TRUE e FALSE nei JSON
I valori booleani (true) e (false) sono scritti negli oggetti JSON senza le virgolette (“) e sempre in minuscolo … per distinguerli da valori alfanumerici.
Per ottenere il valore true e false dobbiamo ricorrere alla aspecifica “format JSON”, per indicare che il valore dell’ultimo JSON è già un 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
}
]
}
Esempio Nr. 9 – Creazione di un HTTP Header per le funzioni QSYS2.HTTP
Delle funzioni QSYS2.HTTP per consumare Web Service via SQL ne ho parlato nel post “New QSYS2.HTTP Functions SQL (7.4 TR5 and 7.3 TR11)“, riporto qui sotto una parte di codice RPG dove utilizzo QSYS2.HTTP_POST per chiamare dei web services di un sistema di pagamento. Utilizzo delle variabili per URL, Header, Body e Response per poter poi loggare la chiamata in apposite tabelle di log
..
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)) ));
...
Esempio Nr. 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
è possibile creare una DS clob e salvare il json ricevuto dentro questa ‘struttura’ con array?