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

Hi Jay,

can you post the complete procedure? Normally the error is somewhere else, but I can't say.

If not, try to reduce the SP to the absolutely necessary, and try again.

Where is the value of g_ordersArr coming from? Is that JSON really so flat, that you can access the value with the path '$.destAddr1'? A reduced example of the JSON value would be good.

Even as I found no APAR that documents any failure of JSON_UPDATE within a stored procedure, try to install the latest DB group PTFs to make sure, that there is no problem.

Which version / release of the OS and which TR level are you running on?

Helping is a lot easier with more information on hand. We're really trying to help here, but that's kinda hard without more information.

Regards,
Daniel

Am 21.07.2025 um 23:13 schrieb Jay Vaughn <jeffersonvaughn@xxxxxxxxx>:

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

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