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

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

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