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

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