Re: json_table - get entire array

[Jay Vaughn <jeffersonvaughn@xxxxxxxxx>]

Birgitta,

Yes, and you are kinda making the point for me.
That could get quite verbose based on everything under this single json I'm
targeting.
So was hoping for a simple way to just point at that array (which I'm doing
in my statement), and then say, bring it all back as a string (for example)

thanks

Jay

On Tue, Jul 15, 2025 at 12:29 PM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> It would have been helpful to see the JASON
> ... have you tried this:
>
> select *
> from *json_table*(*trim*(jvaughn.tempjson1)
>                , '$.Orders[*]'
>                Columns (Column1 ... Path ...,
>                                  Column2 ... Path ...,
>                            Nested $.Positions[*]
>                                          Columns(....)
>                            )
>                   ) as t
> where "dtNr" = '78714';
>
> 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: Tuesday, 15 July 2025 17:55
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: json_table - get entire array
>
> I'm curious about a concept with json_table...
>
> Given the below statement (which is invalid due to the "columns('$')")...
> Is there any possible way to have the statement select the entire orders
> array, sub array/objects, etc, without having to define that entire
> array/subarray/objects DS  for that orders array?
>
> select *
> from *json_table*(*trim*(jvaughn.tempjson1)
>                ,'$' columns(NESTED '$.orders[*]'
>                             columns('$')
>                            )
>                   ) as t
> where "dtNr" = '78714';
>
> tia
>
> Jay V
> --
> 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.