Re: json conditional arrays/objects

On Sep 19, 2024, at 2:58 AM, Martijn van Breden <m.vanbreden@xxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi Jay

As an alternative to Birgitta's solution you can also use nullif(<json_object-function>, '{}') or nullif(<json_array-function>, '[]') to get rid of empty objects or arrays.


Kind regards, Met vriendelijke groet,



Martijn van Breden

lead software architect



________________________________
Van: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> namens Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
Verzonden: woensdag 18 september 2024 23:16
Aan: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Onderwerp: json conditional arrays/objects

CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.



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.

--
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.