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

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

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