Re: rpg embedded sql json and escaped output -- RPG400-L

ok everything in the consents object is either escaped, double/triple
escaped...

exec sql

values

nullif(

json_object(key 'input'
//o1s

value nullif(

json_object(
//o2s

key 'phoneNumber'

value trim(:p_phoneNumber)

,'phoneType'

: case when :p_phoneType <> ' '

then trim(:p_phoneType)

end

,key 'metadata'

value nullif(

json_object(
//o3s

'sourceChannel'

: trim(:p_sourceChannel)

,'customerName'

: case when :p_customerName <>
' '

then trim(:p_customerName)

end

,'roleType'

: case when :p_roleType <> ' '

then trim(:p_roleType)

end

,'uaiId'

: case when :p_uaiId <> ' '

then trim(:p_uaiId)

end

,'appId'

: case when :p_applicationId <>
' '

then
trim(:p_applicationId)

end

,'teamMemberId'

: case when :p_teamMemberId <>
' '

then trim(:p_teamMemberId)

end

format json

absent on null)
//o3e

,'{}')

,key 'consents'

value nullif(

json_array(
//a1s

nullif(

json_object(
//o4s

'businessFunction'

: case when :p_businessFunction1 <>
' '

then
trim(:p_businessFunction1)

end format json

,'hasConsented'

: case when :p_businessFunction1 <>
' '

then

case when :p_hasConsented1 =
'Y'

then 'true'

else 'false'

end

end format json

,key 'channels'

value nullif(

json_array(
//a2s

nullif(

json_object(
//o5s

'communicationsChannel'

: case when :p_communicationChannel <>
' '

then
trim(:p_communicationChannel)

end format json

,'hasConsented'

: case when :p_communicationChannel <>
' '

then

case when :p_smsPhoneConsent =
'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o5e

,'{}')

format json

)
//a2e

,'[]')

absent on null)
//o4e

,'{}')

,nullif(

json_object(
//o6s

'businessFunction'

: case when :p_businessFunction2 <>
' '

then
trim(:p_businessFunction2)

end format json

,'hasConsented'

: case when :p_businessFunction2 <>
' '

then

case when :p_hasConsented2 =
'Y'

then 'true'

else 'false'

end

end format json

absent on null)
//o6e

,'{}')

format json

)
//a1e

,'[]')

,'updatedAt'

:trim(:p_rcdEffectiveTimestamp)

format json

absent on null)
//o2e

,'{}')

format json

absent on null)
//o1e

,'{}')

concat '}' // end query

into :g_clob;

tia

Jay

On Fri, Sep 20, 2024 at 11:27 AM Jay Vaughn <jeffersonvaughn@xxxxxxxxx>
wrote:

it is quite a beast - I'll try but there is no CTE here with this one

Jay

On Fri, Sep 20, 2024 at 11:14 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Could you show the SQL-Statement in embedded SQL?
Could it be you are using a Common Table Expression or nested sub-selects,
for generating JSON information that is used in the final SELECT
statement?
If so you have to add FORMAT JSON after each generated JSON information
used
in another Common Table Expression or nested Sub-Select or final SELECT
statement.

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: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay
Vaughn
Sent: Friday, 20 September 2024 16:20
To: RPG programming on the IBM i (AS/400 and iSeries)
<rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: rpg embedded sql json and escaped output

I have an rpg pgm doing some embedded sql and using json_object.

It consists only of about 5 fields all char fields...

In debug, when I view the output it is escaped.

When I code this same json_object in ACS run sql scripts (with hard coded
values), there are no escape characters in the output.

What is causing the escape char in my output in by rpg pgm when debugging?

Also, there is more json around this json_object, and this particular
json_object is nested... but still, none of the other structures should
impact this one in regards to the escaping, correct?

I have tried "format json" for each field as well with no luck - and I do
not even had to specify "format json" in my ACS run sql.

any clarification would be appreciated.

thanks

Jay
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.