Re: json_table - get entire array -- MIDRANGE-L

thank ya Vern... yeah, doing a simple string type hunt/peck/replace crossed
my mind...
but think I better stick to db2 json functions when avail.

I'm now playing with this...

values
systools.bson2json(
systools.*json_update*(
systools.json2bson(
jvaughn.ordersArr
)
, '{ $set: {"destAddr1":"xxx"}}'
)
);

but getting this error at runtime... and not exactly sure why!?

Message ID . . . . . . : CPF503E Severity . . . . . . . : 30

Message type . . . . . : Notify

Date sent . . . . . . : 07/17/25 Time sent . . . . . . :
08:35:06

Message . . . . : User-defined function error on member QSQPTABL.

Cause . . . . . : An error occurred while invoking user-defined function

JSON_UPDATE in library SYSTOOLS. The error occurred while invoking the

associated external program or service program QSQJSON in library QSYS,

program entry point or external name jsonUpdate2, specific name
JSON_00001.
The error occurred on member QSQPTABL file QSQPTABL in library QSYS2. The

error code is 1. The error codes and their meanings follow:

1 -- The external program or service program returned SQLSTATE 38553.
The
text message returned from the program is: JSON parsing error code: 9
for: .
.set: ."destAddr1":"xxx".. .

ordersArr is really just a json object like this...

{ "destAddr1" : "existingValue" }

and I'm trying to update it with 'xxx'

thanks

Jay

On Wed, Jul 16, 2025 at 2:38 PM Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hey Jay

I've used the *NIX sed utility to replace things within IFS files. I
don't know if there is an SQL way to make that call - the QShell version
doesn't do the replace in place, the PASE one does, as I recall.

Just a thought, man!

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.tQYG2QnF.2v2q6zzt@centurylink.net> CAAC
(COMMON Americas Advisory Council) IBM Influencer 2023

On 7/16/2025 10:06 AM, Jay Vaughn wrote:

also while we are on the topic - and I have never seen this before but
wondering if possible...

can you update an json?

is there a such a db2 sql statement where you can say... update myJson,

set

path/key value = 'foo'?

or can db2 json only be derived from a db2 table?

thanks

Jay

On Wed, Jul 16, 2025 at 10:53 AM Jay Vaughn<jeffersonvaughn@xxxxxxxxx>
wrote:

Daniel - that was right on the money!

I sincerely appreciate your help on this.

thanks

Jay

On Wed, Jul 16, 2025 at 10:16 AM Daniel Gross<daniel@xxxxxxxx> wrote:

Hi Jay,

as Birgitta and I said repeatedly - it's quite hard to "imagine" what

you

have and want - you should at least provide some "example" data. So -

here

is, what I "interpret" from the given information:

select *
from json_table('{
"orders":[
{"dtNr":"4711", "other_item":"aaa"},
{"dtNr":"0815", "other_item":"bbb"},
{"dtNr":"78714", "other_item":"ccc"}
]
}',
'$.orders[*]' columns(
"dtNr" varchar(20),
"json_data" varchar(32000) format json path '$'
)
)
where "dtNr" = '78714';

This gives you 2 columns - the "dtNr" and a column "json_data" that
contains the whole "orders" array element which has the given "dtNr".

But your mileage may vary - as I simply have "interpreted" what you

have

written. So if it works - fine - if not - please send more information
about your JSON - shorten it, change the data, whatever makes sense.

HTH
Daniel

Am 16.07.2025 um 15:12 schrieb Jay Vaughn<jeffersonvaughn@xxxxxxxxx>:

errr- actually that is not it - it is pulling the orders array for

all

order arrays and not just the orders array that contains dtNr = 7814

here is a very watered down version of the json that would illustrate

the

paths I'm after...

{
orders[
{
dtNr
}
]
}

so I ultimately only want to retrieve the entire orders array that has

the

dtNr = 7814

Jay

On Wed, Jul 16, 2025 at 8:58 AM Jay Vaughn<jeffersonvaughn@xxxxxxxxx

wrote:

got it to work - and yes Daniel I totally agree that providing the

json

would have been more helpful - sorry I couldn't was just too big with

too

much sensitive data...

this is what I was looking for...

select *
from *json_table*(jvaughn.tempjson1
,'$'
Columns (orders *varchar*(*32000*) format json Path
'$.orders'
,nested '$.orders[*]'
Columns("dtNr" *char*(*10*)
)
)
) as t
where "dtNr" = '78714';

Jay

On Wed, Jul 16, 2025 at 7:48 AM Jay Vaughn<jeffersonvaughn@xxxxxxxxx

wrote:

Daniel - silly of me thinking ArrayData was some kind of reserved

word...

But the answer to your question is in the path... the key name of

the

array is orders.

so If I replace ArrayData with "orders" - I still get null...

Select *
From *JSON_TABLE*(jvaughn.tempjson1
,'$' columns(NESTED '$.orders[*]'
Columns("dtNr" *char*(*10*)
,"orders" *VarChar*(
*32000*) format json Path '$.orders'
)
)
)
where "dtNr" = '78714';

thanks

Jay

On Wed, Jul 16, 2025 at 12:02 AM Daniel Gross<daniel@xxxxxxxx>

wrote:

So what's the "name" (key) of the JSON element containing the

array?

Really "ArrayData"? Maybe the JSON path '$.orders' of the element

is

wrong?

Without seeing at least some part of the structure it's hard to say
where the error is. From what you write, it seems like it looks

like

this:

{
"orders":[
"drNr":"xxxxxx",
"ArrayData:[
...
]
]
}

That the "dtNr" column is populated is a good sign - so it seems

correct

until there.

Regards,
Daniel

Am 15.07.2025 um 22:57 schrieb Jay Vaughn <

jeffersonvaughn@xxxxxxxxx>:

this is great Birgitta (or anyone avail to answer)..

but my arraydata comes back null... however my dtNr is found! I

need

the

associated array that dtNr is found in...

Select *
From *JSON_TABLE*(jvaughn.tempjson1
,'$' columns(NESTED '$.orders[*]'
Columns("dtNr" *char*(*10*)
,ArrayData

*VarChar*(*32000*)

format json Path '$.orders'
)
)
)
where "dtNr" = '78714';

tia

Jay

On Tue, Jul 15, 2025 at 4:26 PM Jay Vaughn <

jeffersonvaughn@xxxxxxxxx

wrote:

I actually think I got it...

lots of thanks Birgitta!

Jay

On Tue, Jul 15, 2025 at 4:06 PM Jay Vaughn <

jeffersonvaughn@xxxxxxxxx

wrote:

thank - this looks like interesting stuff I've never used before

so I

have hope...

however both of these statements fail as-is (when replacing

json_document

with my actual json resource)

Jay

On Tue, Jul 15, 2025 at 1:01 PM Birgitta Hauser <

Hauser@xxxxxxxxxxxxxxx>

wrote:

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 contactsupport@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 contactsupport@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 contactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@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.