Re: json_update - does it work?

I'm not frustrated - yes json_update works - and I'd get it working for one
use case, but then stumble on another use case (ie. update a single
element, append an array to another json, delete an entire array entity
from the json, etc)

I am appreciative of yours and Birgitta's replies - they have helped

seems you may just keep seeing the same post pop up and not reading the
question (and addressed that, assuming it was the same original question...
instead of the actual question I was asking at the time)

BUT I completely fulfilled all my tasks today with json_upd ate and all
questions are now answered.

thanks

Jay

On Wed, Jul 30, 2025 at 11:28 AM Daniel Gross <daniel@xxxxxxxx> wrote:

Hi Jay,

I'm not aggravated at all, because my JSON_UPDATE code is working fine.

But I can understand your frustration - some 16+ mails back and forth,

and

still no solution out of nowhere. If you recall correctly, Birgitta and

me

helped all along the way, but at some point, it's hard to help someone

who

doesn't want to help himself.

Why is the JSON doc and the exact source so important?

Simply because JSON_UPDATE doesn't use "lax" JSON path mapping - it only
uses implicit "strict" path mapping - and therefore a small inaccuracy
between the JSON document and the JSON path in the JSON_UPDATE function
will result in an error.

But possibly, you already know all this.

So good luck - if you want me to help, just send a more specific request,
and I will try my very best.

Kind regards,
Daniel

Am 30.07.2025 um 13:54 schrieb Jay Vaughn <jeffersonvaughn@xxxxxxxxx>:

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.

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