Re: json_update - does it work?

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