Re: json_update - does it work? -- MIDRANGE-L

Daniel,

my code looks 99% better than most.
If you are "tired" take a break buddy - surely someone else has some
json experience in here.

I provided all the code you need.

you can put it in ACS "as-is" and in an sql stored procedure and you'll see
how it does not work in the sql stored proc.

but for you, I'd suggest you just ignore these if they are stressing you
out.

I never understood how people have to reply to emails that aggravate them
as opposed ot just ignoring them.

and if you say you are just trying to help, these type replies do not. -
Now I'm too busy for negativity so you won't get anymore of this back from
me. sorry

Jay

On Tue, Jul 29, 2025 at 3:55 PM Daniel Gross <daniel@xxxxxxxx> wrote:

Hi Jay,

I think we did the drill some weeks ago - again and again.

Please post the complete code - probably reduced to and with shortened and
anonymous data. Without that, it's a complete guessing game for us.

After about some 20 mails back and forth, reading that last mail of you
really makes me think "Does he really expect us to guess again?"

So if you want real help - and not random wild guesses - please reduce
your procedure to the shortest form, that replicates the problem - shorten
and anonymize your JSON data - and post it all here with some information
about the IBM i version and release that you are using.

I'm really willing to help - but I don't like "random wild guessing based
on incomplete information".

You don't have to be ashamed because you think your code might not look
perfect - we are all here to learn and help each other - no judgement.

And to answer your initial question - yes, JSON_UPDATE does work if used
correctly.

Kind regards,
Daniel

Am 29.07.2025 um 21:33 schrieb Jay Vaughn <jeffersonvaughn@xxxxxxxxx>:

yeah, it is not that - compiled with commit(*none)

thanks for the consideration.

thanks

Jay

On Tue, Jul 29, 2025 at 2:03 PM Peter Dow <petercdow@xxxxxxxxx> wrote:

Commitment control? If the procedure is under commitment control, and
does not do a COMMIT, then any changes it made would be discarded.

--
*Peter Dow* /
909 793-9050
petercdow@xxxxxxxxx
/

On 7/29/2025 5:33 AM, Jay Vaughn wrote:
one more lingering strange issue...

the following code works fine in ACS (run sql scripts) but does not

appear

to update the destAddr1 field when this same exact sql statement is

inside

an sql procedure - any ideas why not?

create or *replace* variable jvaughn.ordersArr *clob*(*250*m);

set jvaughn.ordersArr = *json_update*(

jvaughn.ordersArr
,'SET'

,'$.destAddr1'

,'newValue1'

);

and obviously jvaughn.ordersArr has the same value for each at runtime.

tia

Jay V

On Tue, Jul 22, 2025 at 10:28 AM Jay Vaughn<jeffersonvaughn@xxxxxxxxx>
wrote:

got it guys! I needed this...

'$.dtNr'

that is all!

jay

On Tue, Jul 22, 2025 at 8:51 AM Jay Vaughn<jeffersonvaughn@xxxxxxxxx>
wrote:

ok, I backed up out of the sp and went back to ACS...

what is actually happening in ACS is this...

values
*json_update*(
jvaughn.json2
,'SET'
,'$.orders'
,*trim*(jvaughn.ordersArr)
);

in this statement I want to append the json orders array
(jvaughn.ordersArr) into an existing json (jvaughn.json2)

At the time of this statement jvaughn.json2 looks like this...

{
orders[
{order1}
,{order2}
,{etc}
]
}

but after the above json_update runs (hoping to append to the

orders),

it

replaces them all with my single jvaughn.ordersArr..

is there a different reserved word I should be using in this case

instead

of SET?

thanks

Jay

On Tue, Jul 22, 2025 at 2:18 AM Birgitta Hauser<

Hauser@xxxxxxxxxxxxxxx

wrote:

What's the job CCSID where your JSON statement is running?
Perhaps 65535 (=without CCSID)? If so, try to change it to a real

CCSID

(for example 37).

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: Monday, 21 July 2025 23:13
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: Re: json_update - does it work?

so got the json_update to work in ACS...

Wondering why this works in ACS...
(after it runs, it displays the new jvaughn.ordersArr, with the new

value

assigned)

set jvaughn.ordersArr = 'some json';

values
*json_update*(
jvaughn.ordersArr
,'SET'
,'$.destAddr1'
,'newValue1'
);

But if you put the same in an SQL proc and debug it...
(g_ordersArr is populated in debug when you hit the json_update
statement - then after it runs, sqlcode = 0 but g_ordersArr = ' ')

declare g_ordersArr clob(500m);
values
*json_update*(
g_ordersArr
,'SET'
,'$.destAddr1'
,'newValue1'
)
into g_ordersArr;

I even tried doing an "into g_ordersArr2" - a separately declared

memory

storage.
Also switched this up to a set g_ordersArr = json_update...
but same result.

any ideas?

thanks

Jay

On Thu, Jul 17, 2025 at 3:20 PM Daniel Gross<daniel@xxxxxxxx>

wrote:

Are you sure about TR8?

IIRC the JSON_UPDATE function came with 7.4 TR7 - but I could be

wrong.

->

https://www.itjungle.com/2022/11/02/trs-deliver-database-and-sql-updat

es-for-every-ibm-i-user/

It might be the best idea, to bring the machine to the latest 7.4

TR

at least.

Regards,
Daniel

Am 17.07.2025 um 20:27 schrieb Jay Vaughn <

jeffersonvaughn@xxxxxxxxx

:

7.4 TR8

I presume this box needs a TR?

Jay

On Thu, Jul 17, 2025 at 2:10 PM Daniel Gross<daniel@xxxxxxxx>

wrote:

The JSON_UPDATE function came for release 7.4 in December 2023 -
and

it's

a system function - it has no schema to prefix.

Which release and TR have your boxes?

Your welcome
Daniel

Am 17.07.2025 um 19:52 schrieb Jay Vaughn
<jeffersonvaughn@xxxxxxxxx

:

thanks Daniel - but it is not a matter of the syntax (yet)...

if I do not qualify a lib when using json_update it simply

cannot

find

the

scalar function and I get an error.

If I go look for json_update, I find it in systools (two

different

boxes

-

same way).

apparently the systools version is not working for me.

so what lib is your json_update found in?

thanks

Jay

On Thu, Jul 17, 2025 at 1:35 PM Daniel Gross<daniel@xxxxxxxx>

wrote:

Hi Jay,

I don't know, which JSON_UPDATE you have found - but this here
works

for

me:

values
json_update(
'{"destAddr1":"foo"}', --> this is the JSON

doc

'SET', --> set a

new

value

'$.destAddr1', --> the JSON path

to

the

object
'bar' --> the new

value

);

With 'SET' the value is replaced with the new value if it

already

exists -

if it doesn't exist, it will be created at the specified path.

Another option is 'REMOVE' - of course you don't need the new
value

with

'REMOVE'.

The path is always treated "strict" - "lax" is not permitted

with

JSON_UPDATE.

The result is CLOG(2G) by default.

->

https://www.ibm.com/docs/de/i/7.6.0?topic=functions-json-updat

-> e

Which release do your machines have? AFAIK since release 7.4

the

JSON_UPDATE function is a system function - not SYSTOOLS.

HTH and regards,
Daniel

Am 17.07.2025 um 19:07 schrieb Jay Vaughn <

jeffersonvaughn@xxxxxxxxx

:

there is a version of json_update on both my boxes in

systools

- and examples I look at online show it in systools too.

BUT I totally believe you Birgitta and I'll go look and see
where

else

I

can find it.

Jay

On Thu, Jul 17, 2025 at 12:46 PM Birgitta Hauser <

Hauser@xxxxxxxxxxxxxxx>

wrote:

JSON_UPDATE is not in the SYSTOOLS schema. It is a SQL

standard

function:

https://www.ibm.com/docs/en/i/7.6.0?topic=functions-json-update

The following example works for me:
Create Or Replace Table qtemp.JSONCity (JSONText

VarChar(16000)

CCSID 1208 Default '');

Insert into JSONCity
Values('{
"EmployeePerCity": {
"Town": [{
"PostCode": "77880",
"TownName": "Sasbach",
"Attendee": [{
"Name": "Lehmann Maria",
"Street": "Schwarzwaldstr. 26"
}]
},
{
"PostCode": "63128",
"TownName": "Dietzenbach",
"Attendee": [{
"Name": "Bauer Herrmann",
"Company": "Große Kartoffeln

GmbH",

"Street": "Wald-und-Wiesen-Weg

17"

},
{
"Name": "Fischer Fritz",
"Company": "Fisch und Angel

GmbH",

"Street": "Am Bach 7"
},
{
"Name": "Hauser Birgitta"
}]
},
{
"PostCode": "86916",
"TownName": "Kaufering",
"Attendee": [{
"Name": "Straub Elli",
"Company": "Meier und Sohn",
"PostBox": "12345"
},
{
"Name": "Huber Anna",
"Street": "Nordring 4b",
"PostBox": "11223"
},
{
"Name": "Schmidt Ludwig"
},
{
"Name": "Janssen Jan"
}]
}]
}
}') with nc;

Update JSONCity
set JSONTEXT = JSON_Update(JSONTEXT, 'SET',
'$.EmployeePerCity.Town[1].Attendee[0].Name', 'Gerber,

Anton')

Limit 1 With NC;

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: Thursday, 17 July 2025 18:19
To: Midrange Systems Technical Discussion <

midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Re: json_update - does it work?

what is weird is that on both boxes...

if you look at the functions under schema systools (or on
qsys2.sysroutines), you see 2 versions of json_update...
one that requires 5 parms and one that requires 2 parms...

So I guess my question has to be directed to anyone that has

successfully

used json_update and how?

thanks

Jay

On Thu, Jul 17, 2025 at 12:05 PM Jay Vaughn <

jeffersonvaughn@xxxxxxxxx>

wrote:

any reason why this simple scenario does not work?

tried this simple scenario on 2 different IBM i's and both
error

the

same.

create or *replace* variable jvaughn.jsonObj

*clob*(*1*g);

set jvaughn.jsonObj = '{"destAddr1":"foo"}'; set
jvaughn.jsonObj = systools.bson2json(

systools.*json_update*(

systools.json2bson(jvaughn.jsonObj)
,'{ $set: {"destAddr1":"bar"}}'
)
);

I get this error (on both boxes)

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

.

. :

30

Message type . . . . . : Notify

Date sent . . . . . . : 07/17/25 Time sent . . . .

.

. :

12:01:25

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":"bar".. .

2 -- The external program failed before it completed.

3 -- The database timed out waiting for the program to

return.

The

timeout

More...

thanks

Jay

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