Forum Stats

  • 3,768,172 Users
  • 2,252,755 Discussions
  • 7,874,481 Comments

Discussions

Generate json with boolean from table with varchar2 mimicking boolean?

Paavo
Paavo Member Posts: 675 Silver Badge

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


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

«1

Answers

  • Paavo
    Paavo Member Posts: 675 Silver Badge

    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

  • Paavo
    Paavo Member Posts: 675 Silver Badge

    After reading old thread in ask Tom made small function based on the code Tom wrote there.

    Is this today necessary in R19c ? And again what could go wrong? IMHO it is more safe to typecast true to "true" than to e.g. 1 because that can't be safely mass-replaced in clob.

    create or replace
      function f_clob_replace( i_clob in clob
      ,i_what in varchar2
      ,i_with in varchar2 )
      return clob
      as
      n number;
      o_clob clob;
      begin
      o_clob := i_clob;
      n := dbms_lob.instr( o_clob, i_what );
      if ( nvl(n,0) > 0 )
      then
      dbms_lob.copy( o_clob
      ,o_clob
      ,dbms_lob.getlength(o_clob)
      ,n+length(i_with)
      ,n+length(i_what) );
     
      dbms_lob.write( o_clob
      ,length(i_with)
      ,n
      ,i_with );
      if ( length(i_what) > length(i_with) )
      then
      dbms_lob.trim( o_clob,
      dbms_lob.getlength(o_clob)-(length(i_what)-length(i_with)) );
      end if;
      end if;
      return o_clob;
      end f_clob_replace;
    
    create or replace editionable view v_json_test_bool
    as
    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
      ,json_object( srctbl.* returning clob format json) varchar2_json
      ,replace(replace(json_object( srctbl.* returning clob format json),'"true"','true'),'"false"','false') replace_bool_json
      ,f_clob_replace(f_clob_replace(json_object( srctbl.* returning clob format json),'"true"','true'),'"false"','false') f_clob_replace_bool_json
      from srctbl
    
    drop table ctas_v_json_test_bool
    create table ctas_v_json_test_bool as
    select * from v_json_test_bool
    bool_true	bool_false	POST_SB_JSON	VARCHAR2_JSON	REPLACE_BOOL_JSON	F_CLOB_REPLACE_BOOL_JSON
    true	false	{"bool_true":"true"}	{"bool_true":"true","bool_false":"false"}	{"bool_true":true,"bool_false":false}	{"bool_true":true,"bool_false":false}
    
    desc ctas_v_json_test_bool
    
    Name Null? Type
    ------------------------ ----- -------
    bool_true CHAR(4)
    bool_false CHAR(5)
    POST_SB_JSON CLOB
    VARCHAR2_JSON CLOB
    REPLACE_BOOL_JSON CLOB
    F_CLOB_REPLACE_BOOL_JSON CLOB
    
    
  • Paavo
    Paavo Member Posts: 675 Silver Badge

    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
    jariola Member Posts: 10,455 Silver Crown
    edited Sep 24, 2021 7:01AM

    For me it's not at all clear what is the issue or what problem you try resolve.

    But have you just try query your test data table

    select 
      json_object( 'some_boolean' value "Some_boolean" format json) as json_obj
    from ctas_otn_post_json2
    

    Or

    select 
      json_object(
        'some_number' value "Some_number"
        ,'some_boolean' value "Some_boolean" format json
        returning clob 
      ) as json_obj
    from ctas_otn_post_json2
    
    Paavo
  • Paavo
    Paavo Member Posts: 675 Silver Badge

    @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
    jariola Member Posts: 10,455 Silver Crown

    Unfortunately, I don't know how solve your problem.

    I see that select * ... is bad coding habit =)

  • Paavo
    Paavo Member Posts: 675 Silver Badge

    @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
    jariola Member Posts: 10,455 Silver Crown
    edited Sep 24, 2021 8:15PM

    Hmm. I'm not export. Your latest example not working on apex.oracle.com 18c, as I quickly tested. It gives ORA-02000: missing VALUE keyword.

    Below works and boolean is without double quotes in column that alias is post_sb_json

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


    Paavo
  • Paavo
    Paavo Member Posts: 675 Silver Badge

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


  • Paavo
    Paavo Member Posts: 675 Silver Badge

    @jariola and yes the order of parameters matter, but for some reason they fail for the .* (bad :) ) notation for bad_jariola_json. If that syntax would be ok, then we would have a "native" no-replace-func solution which feels a bit hack over the clob json.

    with srctbl as (select 'true' "bool_true"
      ,'false' "bool_false"
      from dual)
      select srctbl."bool_true"
      ,srctbl."bool_false"
      ,json_object( 'some_boolean' value srctbl."bool_true" returning clob format json ) vaapo_json
      ,json_object( 'some_boolean' value srctbl."bool_true" format json returning clob) jariola_json
      ,json_object( srctbl.* returning clob format json) bad_vaapo_json
    -- ,json_object( srctbl.* format json returning clob) bad_jariola_json --ORA-40448: expecting RETURNING clause
      from srctbl
    
    
    

    rgrds Paavo