Re: json conditional arrays/objects

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

I've applied the nullif's and absent on null's to solve more problems...
half way...

When blanks passed to the values the json works as should and does not
include any of the fields/values... however when values are passed,
everything is escaped in those structures.

How do I get rid of the escapes?
What is causing them?

tia

Jay

On Thu, 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.