There are some samples in "Generating JSON data" in IBM Docs.
A simple example is shown below.
- SQL
SELECT
JSON_OBJECT('customer' VALUE
JSON_ARRAYAGG(
JSON_OBJECT('custNo' VALUE REGNO,
'custInfo' VALUE
JSON_OBJECT(
'custNameKj' VALUE TRIM(KJNAME),
'custNameKana' VALUE TRIM(KNNAME),
'custAddr1' VALUE TRIM(ADDR1),
'custAddr2' VALUE TRIM(ADDR2)
)
)
)
) AS JSONOUT
FROM DEMOLIB.PERSON
WHERE REGNO < 3
;
- JSON (Formatted)
{
"customer":[
{
"custNo":1,
"custInfo":{
"custNameKj":"滝川 厚",
"custNameKana":"タキガワ アツシ",
"custAddr1":"賀茂郡南伊豆町",
"custAddr2":"一色3-16"
}
},
{
"custNo":2,
"custInfo":{
"custNameKj":"川西 節男",
"custNameKana":"カワニシ セツオ",
"custAddr1":"甲賀市",
"custAddr2":"甲南町野尻1-17-4"
}
}
]
}
------------------------------
矢作 英幸
------------------------------
Original Message:
Sent: Wed June 26, 2024 09:34 PM
From: Satid S
Subject: JSON_OBJECT
Dear Sarvan
Please try using JSON_ARRAYAGG in place of JSON_ARRAY to see if it addresses your issue?
This cheat sheet may be useful for you in the future: DB2 for i SQL JSON_OBJECT Essential Cheat Sheet at https://blog.faq400.com/en/db2-for-i/db2-for-i-sql-json_object-essential-cheat-sheet-2/
------------------------------
Satid S
Original Message:
Sent: Wed June 26, 2024 12:02 PM
From: Sarvan Veluppillai
Subject: JSON_OBJECT
Hi
I am trying to convert all records in a physical file to json object. here is what I have in my SQLRPGLE program.
dcl-s json varchar(20000);
exec sql
SELECT JSON_OBJECT('Data' value JSON_ARRAY(JSON_OBJECT(
'RCDID' : RCDID,
'KeyName' : KeyName
)))
INTO :json
FROM va967.Ext001F;
The variable is only have one record. How do I get all the records in to this variable?
Here is the result: {"Data":[{"RCDID":"CPDLTR","KeyName":"CONSTANT"}]}
Any help is much appriciated.
Thanks, sarvan
------------------------------
Sarvan Veluppillai
------------------------------