02 - DB2 for i (EN)02a - SQL (EN)

DB2 for i SQL JSON_OBJECT Essential Cheat Sheet

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"
			]
		},
...

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

--- Roberto De Pedrini Faq400.com
About author

Founder of Faq400 Srl, IBM Champion, creator of Faq400.com and blog.faq400.com web sites. RPG developer since I was wearing shorts, strong IBM i supporter, I have always tried to share my knowledge with others through forums, events and courses. Now, with my company Faq400 Srl, I help companies to make the most of this great platform IBM i.

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *