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.
Index
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.
-- 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));
-- 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
}
]
}
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
}
]
}
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}]}
..
-- 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
));
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;
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
}
]
}
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 Con un piccolo trucco anche una semplice istruzione SELECT può eseguire qualsiasi comando di sistema ! Vediamo come...
Una mini-guida a puntate per la configurazione, gestione, uso e risoluzione dei problemi di IBM i NetServer
Una mini-guida a puntate per la configurazione, gestione, uso e risoluzione dei problemi di IBM i NetServer
Una mini-guida a puntate per la configurazione, gestione, uso e risoluzione dei problemi di IBM i NetServer
Una mini-guida a puntate per la configurazione, gestione, uso e risoluzione dei problemi di IBM i NetServer
Una mini-guida a puntate per la configurazione, gestione, uso e risoluzione dei problemi di IBM i NetServer
View Comments
è possibile creare una DS clob e salvare il json ricevuto dentro questa 'struttura' con array?