Count values between double quotes and brackets

How can I group by and count the values separated by double quotes between the brackets? I have 400K rows, so I’m also concerned about performance.

    ["853","1800"]
    
    ["852","1500"]
    
    ["833","1800"]
    
    ["857","1820"]
    
    ["23468","3184"]
   .....

Desired output:

Value   Count
23468    1212
09692     987
...       ...

Answer

Do you mean something like this? (The with clause is only for testing – remove it, and use your actual table and column names in the main query.)

with
  sample_data (j_arr) as (
    select '["853","1800"]'        from dual union all
    select '["852","1500"]'        from dual union all
    select '["833","1800"]'        from dual union all
    select '["857","1820"]'        from dual union all
    select '["23468","3184"]'      from dual union all
    select '["013", "013", "013"]' from dual
  )
select str, count(*) as ct
from   sample_data cross apply json_table(j_arr, '$[*]' columns str path '$')
group  by str
order  by ct desc, str  --  or whatever you need
;

STR       CT
-------- ---
013        3
1800       2
1500       1
1820       1
23468      1
3184       1
833        1
852        1
853        1
857        1

I am sorry, but I have no clue what “register” means in this context. If you mean that you have 400K rows, I can’t see how performance would be an issue. A quick test on my system (with 402K rows) took about 0.33 seconds.