RE: json_table - get entire array

If you only want to have a string, that contains everything in an object or array, you just have to add FORMAT JSON:

Select *
From JSON_TABLE(Json-Document, '$'
Columns(ArrayData VarChar(4096) FORMAT JSON Path '$.Orders');

You Could also use JSON_QUERY.

Values(JSON_Query(Json_Document FORMAT JSON '$.Orders');


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 18:48
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: json_table - get entire array

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.

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