I try to avoid "repeating myself" and create jsons from tables with minimal effort. But the boolean-type is hard to get right. What could be the best way to create the "POST_JSON" with minimal effort from the fields but with varchar2_to_boolean ?
I have been reading this, but
<a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA" alt="JSON Developer's Guide type="undefined">JSON Developer's Guide</a> (0 Bytes)
create table ctas_otn_post_json2 as
with json_response as (select '{"Some_boolean" : true, "Some_number" : 1234}' as validjsonx from dual)
select json_response.validjsonx
,jt."Some_boolean"
,jt."Some_number"
from json_response
,json_table(json_response.validjsonx, '$[*]'
columns("Some_boolean" varchar2 path '$.Some_boolean'
,"Some_number" number path '$.Some_number')) jt
desc ctas_otn_post_json2
Name Null? Type
------------ ----- --------------
VALIDJSONX CHAR(45)
Some_boolean VARCHAR2(4000)
Some_number NUMBER
select srctbl.*
,json_object( srctbl.* returning clob format json) post_json
,json_object( 'Some_boolean' VALUE CASE WHEN 1=1 THEN 'true' ELSE 'false' END FORMAT JSON) neat_boolean
,json_object( 'Some_boolean' VALUE CASE WHEN srctbl."Some_boolean"='true' THEN 'true' ELSE 'false' END FORMAT JSON) neat_boolean
from (select "Some_boolean","Some_number" from ctas_otn_post_json2) srctbl
Some_boolean Some_number POST_JSON NEAT_BOOLEAN NEAT_BOOLEAN_1
true 1234 {"Some_boolean":"true","Some_number":1234} {"Some_boolean":true} {"Some_boolean":true}
rgrds Paavo