My last post on splitting strings conceded that “a combined SQL + PL/SQL solution beats the best pure SQL solution.” Wrong! I didn’t try JSON_TABLE.
Pretend it’s an array
It’s not hard to reformat a string like
to be a JSON array:
select replace(json_array('a,b,c,d,e,"'), ',', '","') jstr from dual; JSTR -------------------------- ["a","b","c","d","e","\""]
Notice that the double quote in the data itself is properly escaped by the call to JSON_ARRAY. Then I turn the string into a series of array elements by putting double quotes around each comma. Once all that is done, JSON_TABLE will split it like this:
select subs from json_table( replace(json_array('a,b,c,d,e,"'), ',', '","'), '$[*]' columns ( subs varchar2(4000) path '$' ) ); SUBS ---- a b c d e "
So the escaped data is automatically unescaped. Now for speed, here is my test harness:
set serveroutput on declare l_num number; l_timestamp timestamp; l_plsql_secs number; l_JSON_secs number; l_num_substrings number := 10; procedure do(p_sql in varchar2) is begin dbms_output.put_line(p_sql); execute immediate p_sql; end do; begin select count(*) into l_num from user_tables where table_name = 'T'; if l_num > 0 then do('drop table t purge'); end if; do('create table t(id number, str varchar2(4000)) cache'); insert into t select level, to_char(level,'fm000000000')||',000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010' from dual connect by level <= 10000; commit; dbms_output.put_line('Substrings' || chr(9) || 'JSON' || chr(9) || 'PL/SQL'); for i in 1..10 loop select count(*) into l_num from t; l_timestamp := localtimestamp; select count(column_value) into l_num from ( select id, column_value from t, table(string_tokenize(str, ',')) ); l_plsql_secs := extract(second from localtimestamp - l_timestamp); l_timestamp := localtimestamp; select count(subs) into l_num from ( select id, subs from t, JSON_table( '["' || replace(str, ',' , '","') || '"]', '$[*]' columns( subs varchar2(99) path '$' ) ) ); l_JSON_secs := extract(second from localtimestamp - l_timestamp); dbms_output.put_line(l_num_substrings || chr(9) || l_JSON_secs || chr(9) || l_plsql_secs); update t set str = str || ',000000001,000000002,000000003,000000004,000000005,000000006,000000007,000000008,000000009,000000010'; l_num_substrings := l_num_substrings + 10; commit; end loop; end; /
Notice that I keep the same number of input rows here and increase the number of substrings per row.
PL/SQL does great, but JSON_TABLE appears to beat it.