DB2 for i SQL JSON_OBJECT Essential Cheat Sheet

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