RE: Help with building a nested JSON command

An array is generated with the JSON_ARRAY function.
Within the JSON_ARRAY you just list the values:

Something like this:
Select json_object(key 'CMSNumber' value q100.kcms# -- value "CMSNumber"
, key 'EnteredBy' value q100.kendt -- value "EnteredBy"
, key 'Currency' value case when ccrcd = '' then 'USD'
else ccrcd
end
Key 'User' value JSON_ARRAY(Ke1Usr, KE2USR, Ke3USR, KE4USR, KE5USR))
...

In an Array NULL values are ignored, so specifying absent on NULL in an array is not needed in other words it is the default.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of a4g atl
Sent: Tuesday, 12 August 2025 19:36
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with building a nested JSON command

The closing param is on my statement but is not shown here. Trying to configure json_array.
Darryl.


On Tue, Aug 12, 2025 at 12:38 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

You have 2 (nested) Objects, but only a single closing parenthesis.
IMHO you have to set a closing parenthesis after smnam.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i Database and Software
Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
"Train people well enough so they can leave, treat them well enough so
they don't want to. " (Richard Branson) "Learning is experience …
everything else is only information!" (Albert
Einstein)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
a4g atl
Sent: Tuesday, 12 August 2025 18:20
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Help with building a nested JSON command

I am trying to build a SQL command to be used in a webservice. The
command will have nested JSON objects.

I am unable to figure how to structure the statement. I have viewed a
number of examples, but they are all different and fail to run when I
try them.

Here is my example:

Select json_object(
key 'CMSNumber' value q100.kcms# -- value "CMSNumber"
, key 'EnteredBy' value q100.kendt -- value "EnteredBy"
, key 'Currency' value
case when ccrcd = '' then 'USD'
else ccrcd
end
-- this is the object within the object , 'ErrorUsers' value
json_object ( key 'User' value ke1usr absent on null , key 'User'
value ke2usr absent on null , key 'User' value ke3usr absent on null ,
key 'User' value ke4usr absent on null , key 'User' value ke5usr
absent on null
)

, key 'SalesmanNumber' value ksalno
, key 'SalesmanName' value smnam

from ....

Can anyone assist with what I am doing wrong?
Thanks
Darryl Freinkel
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.