You have to add a few more CASE Clauses:
The following query will return an Empty Array i.e. [] if YourVar1 and
YourVar2 are empty. That means no empty objects are added
Values(JSON_Array(Case When YourVar1 > '' or YourVar2 > '' or ...
Then JSON_Object('Key1' : Case
when YourVar1 > '' Then YourVar1 End,
'Key2' : Case When YourVar2 > '' Then YourVar2 End,
...
Absent on NULL)
End Format JSON));
For more complex JSON documents I'd suggest to use common table expressions.
In the first (few) common table expressions you prepare the raw data and
then start building the JSON-Document from the lowest level ... until you
get the final JSON document in the final select.
The following query will return '' if YourVar1 to YourVar4 are empty
With x (MyObj1, MyObj2)
as (Values(JSON_Object('Key1': Case When '' > '' Then 'ABC' End,
'Key2': Case When '' >
'' Then 'XYZ' End
Absent On Null),
JSON_Object('Key3': Case When '' > '' Then 'AAA'
End,
'Key4': Case When '' >
'' Then 'ZZZ' End
Absent on NULL))),
y (MyArr) as (Select JSON_Array(Case When MyObj1 <> '{}' Then
MyObj1 End Format JSON,
Case
When MyObj2 <> '{}' Then MyObj2 End Format JSON)
from x)
Select Case When MyArr <> '[]'
Then JSON_Object('MyKeyArr': Case When MyArr <> '[]'
Then MyArr End Format JSON
Absent on NULL)
Else ''
End
from y;
@Jon ... with Data-Gen also have to add a few additional IFs!!!
... and once wrapped in a view it is also only a single Statement
in your RPG Program.
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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Wednesday, 18 September 2024 23:17
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: json conditional arrays/objects
Ok I asked a question about this last week and got a good answer back from
Birgitta but I've cleaned my gmail up and cannot find that last post
unfortunately, so starting a new one...
Lets say I have the following json structure embedded in another json
object...
"Array1": [
{
"key1": "myValue1",
"key2": "myValue2",
"Array2": [
{
"key3": "myValue3",
"key4": "myValue4"
}
]
},
{
"key5": "myValue5",
"key6": "myValue6"
}
]
But I only want that array or object to show if non-blanks are passed to any
of "myValues"
and I cannot have empty structures such as {}.
Is there a way to do this with db2 sql json functions?
I used Birgittas instructions on a field basis using syntax such as...
values json_object('key1"
:case when :myValue <> ' '
then trim(:myValue)
end
absent on null);
But this will leave {} if nothing in myValue.
How do I ensure it does not build the empty structure?
tia
Jay
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
