How to get the size of json array in oracle when the entire array is stored as a string

Question

Say I have a table as follows with one row inserted:

create table docs(id number, doc json);
insert into docs values(1,'{
    "name":"Jay",
    "arr1":[1,2,3,4,5],
    "arr2":"[1,2,3,4,5]"
}');

I have a requirement where I need to get the size of arr2 above, though the value of arr2 is a string, is it possible to get the size of the array within that string?

I can do json_value as follows but it returns correct size as 5 only for arr1 and returns 1 for arr2:

SQL> select json_value(doc,'$.arr1.size()') size1, json_value(doc,'$.arr2.size()') size2 from docs;

     SIZE1      SIZE2
---------- ----------
         5          1

I need someway to get the size of arr2 as 5.

Answer 1

Use JSON_VALUE to first extract the string value and then use JSON_VALUE a second time to count the number of elements:

select json_value(doc,'$.arr1.size()') size1,
       json_value(json_value(doc,'$.arr2'), '$.size()') size2
from   docs;

Outputs:

SIZE1	SIZE2
5	5

fiddle