Re: json conditional arrays/objects

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

Birgitta, this is great. 

Thanks for the detailed example. 

And appreciate the tip on using CTE’s when the json gets a little more complex. 

Jay

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