Generate json with boolean from table with varchar2 mimicking boolean?

Not sure if it is wise and elegant to mass replace the values like this from the resulting json. What could go wrong?

with srctbl as (select 'true' "bool_true"
                      ,'false' "bool_false"
                  from dual)
  select srctbl."bool_true"
        ,srctbl."bool_false"
        ,json_object( srctbl."bool_true" returning clob format json) post_sb_json
        ,replace(replace(json_object(srctbl."bool_true" returning clob format json),'"true"','true'),'"false"','false') true_json
        ,replace(replace(json_object(srctbl."bool_false" returning clob format json),'"true"','true'),'"false"','false') false_json
        ,json_object( srctbl.* returning clob format json) varchar2_json
        ,replace(replace(json_object( srctbl.* returning clob format json),'"true"','true'),'"false"','false') boolean_json
  from srctbl

This is because I think the CASE WHEN END styled tricks wont always work and it is not so easy to do those without "repeating myself".

rgrds Paavo

FYI the above code for function which I copied from askTom doesn't replace correctly all values for some reason. Dumped values and in the source varchar2 they were the same etc. tests.

The replace(replace()) does, but I am not sure if that survives larger clobs.

Could there be easier way, e.g. define the field as boolean somehow in the table?

rgrds Paavo

@jariola The challenge is the how json boolean is stored as varchar2 field when turned to table.

When writ ing views over json-originated tables, it is easier to use the t.* notation as below and not tweak all fields one by one. You expect to get correct json with json_object( srctbl.* returning clob format json) but it doesn't because the boolean turns to string.

Or is there boolean type for fields in table?

with srctbl as (select 'true' "bool_true"
  ,'false' "bool_false"
  from dual)
  select srctbl."bool_true"
        ,srctbl."bool_false"
        ,json_object( srctbl.* returning clob format json) varchar2_json
        ,replace(
           replace(json_object(
                      srctbl.* returning clob format json)
                  ,'"true"','true')
          ,'"false"','false') boolean_json
  from srctbl

rgrds Paavo

@jariola I think the .* syntax is lifesaver, imagine you start building views over your original tables and making json_object in the end. Carrying and repeating all those long "GibberishGobbleWobbleThisandThat" in those views is just pure overkill.

the type conversion to plsql boolean doesn't help in views so the varchar2 "true" (or whatever you use for booleans) must be converted in the last step to 'syntactically correct' "GibberishGobbleWobbleThisandThat" : true .

:) without * and we didnt solve anything imho.

with srctbl as (select 'true' "bool_true"
  ,'false' "bool_false"
  from dual)
  select srctbl."bool_true"
  ,srctbl."bool_false"
  ,json_object( srctbl."bool_true" returning clob format json) post_sb_json
  ,replace(replace(json_object(srctbl."bool_true" returning clob format json),'"true"','true'),'"false"','false') true_json
  ,replace(replace(json_object(srctbl."bool_false" returning clob format json),'"true"','true'),'"false"','false') false_json
  from srctbl

https://blogs.oracle.com/sql/post/how-to-store-query-and-create-json-documents-in-oracle-database

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6263249199595

https://stackoverflow.com/questions/3726758/is-there-any-boolean-type-in-oracle-databases

So I went the replace(replace()) path and got things working, without repeating myself but as the above demos show it is not very clear how to do this properly and it seems that Oracle guesses right boolean type in certain cases but when trying to do then once and all then the replace is needed. Luckily replace-function goes clob for clob first parameter, so it seems tobe better alternative than writing custom function for replacing strings (oh sorry varchar2s) :)

rgrds Paavo

@jariola tested too and in Version 19.6.0.0.0 Apex release 21.1.4 it works when it has 32k extended varchar2 support turned on with text search CTXAPP privilege granted. In apex.oracle.com those are not enabled, I think.

--check varchar2 32k (extended) support and CTXAPP privilege and create dataguide text search index for is json constrained field
CREATE TABLE CTAS_OTN_POST_JSON5
(
  VALIDJSONX clob
, "Some_boolean" VARCHAR2(32767 BYTE)
, "Some_number" NUMBER
) ;
ALTER TABLE CTAS_OTN_POST_JSON5
ADD CONSTRAINT CTAS_OTN_POST_JSON5_CHK1 CHECK
(validjsonx is json)
ENABLE;
desc ctas_otn_post_json5;
drop index otn_json5_dgidx1;
insert into ctas_otn_post_json5 (validjsonx,"Some_boolean","Some_number")
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
commit;
select * from ctas_otn_post_json5
CREATE SEARCH INDEX otn_json5_dgidx1 ON CTAS_OTN_POST_JSON5 (VALIDJSONX) FOR JSON;
select * from USER_JSON_DATAGUIDE_FIELDS;
CTAS_OTN_POST_JSON5	VALIDJSONX	$.Some_number	number	4
CTAS_OTN_POST_JSON5	VALIDJSONX	$.Some_boolean	boolean	4


--rebuild search index and enable constraint
--for some reason these codes dont work... ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option
ALTER INDEX otn_json5_dgidx1 REBUILD ('dataguide off');
ALTER INDEX otn_json5_dgidx1 REBUILD ('dataguide on');
ALTER TABLE CTAS_OTN_POST_JSON5 ENABLE CONSTRAINT CTAS_OTN_POST_JSON5_CHK1;

--then try to construct json, and see the difference in json2 and json3
  select srctbl."Some_boolean"
  ,srctbl."Some_number"
  ,json_object( srctbl."Some_boolean" returning clob format json) post_sb_json
  ,replace(replace(json_object(srctbl."Some_boolean" returning clob format json),'"true"','true'),'"false"','false') json1
  ,json_object( srctbl.* returning clob format json) json2
  ,replace(replace(json_object( srctbl.* returning clob format json),'"true"','true'),'"false"','false') json3
  from ctas_otn_post_json5 srctbl

So also the above shows that the replace-trick is needed to fix, see difference with json2 and json3.

rgrds Paavo

ps. for enabling 32k the utl.. script might fail in updating all varchar2 etc. fields due some views according to documentation not sure how this might impact existing json-fields, do those need to be rebuilt etc.?

Also enabling check constraint is json via sqldeveloper add constraint is not declarative and in datamodeler it doesn't capture the search index and is json constraint (ping @thatJeffSmith-Oracle ) so not sure what will happen to relational model get from dictionary and push back to dictionary in the long run if you are not very careful in recreating those constraints and indexes.