Re: building dynamic json

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

Birgitta - this is fantastic... I'm thinking mostly an empty object, as I
will need to condition each field in the object...
but I'll study your options a bit more to help me understand exactly what
my best option may really be.

again this is exactly what I am looking for.

thank you

Jay

On Wed, Sep 11, 2024 at 11:44 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

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