What do you exactly want?
1. Genearate a JSON_OBJECT if there is a value in your variable and if there
is no value (or the value is '') than do not generate any value?
2. Generate an empty Object i.e. {} if there is no value in your varable and
add a key value pair if there is any value?
The following statement will only generate an object if there is any value
in your variable:
Values(Case When :YourVar > '' Then JSON_Object(Key 'YourKey' Value
Trim(:YourVar)) Else '' End);
The following Statement will generate an empty object if there is no value
in your variable:
Values(JSON_OBJECT('YourKey' : Case When :YourVar <> '' then Trim(:YourVar)
End
Absent on NULL));
Note: If Absent on NULL, key values pairs are only included if the value is
not NULL.
Absent on NULL can only be specified once per JSON_OBJECT, so you
have to deal with NULL Vailes (i.e. use the function NULLIF or COALESCE or a
case CLAUSE to set a non-NULL value to a NULL value or the inverse.
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 Jay
Vaughn
Sent: Wednesday, 11 September 2024 16:05
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: building dynamic json
I need to only include the json field if there is a field other than blank
associated with that field's value...
for example, I set out trying to solve this by doing...
values
json_object(
case when :myVal <> ' '
then key 'myVal'
value trim(:myVal)
)
... but no go... compiler does not like this.
So on a field by field basis, how can I continue using the sql json
functions but condition which fields should be included in the json?
tia
Jay
--
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.
midrange.com
