Get array size in oracle sql

I have a table in oracle with this definition

    create table PARAM(
       ID VARCHAR(30),
       DOCUMENT   blob
    )

Document is stored in a json format.

{"id":"value","parameters":[{...},{...},{...}]}

How can i extract size for array parameters in sql?

I need extract array size for each table record, and after sum all this values.

ex:

1 step:

    ID    ARRAY_SIZE
    ----  ----------
    1     10
    2     0
    3     3

2 step:

    TOTAL_RECORDS   TOTAL_ARRAYS_SIZE
    -------------   -----------------
    3               13

Can someone please give me an idea how to do that ?

Answer

Provided you have an is json check constraint on the column, you can use the size() method to get the number elements in an array:

create table t (
  c1 int, 
  c2 varchar2(100) 
    check ( c2 is json ) 
);

insert into t values ( 1, '{ "arr" : [1, 2, 3] }');
insert into t values ( 2, '{ "arr" : [1, 2, 3, 4, 5] }');

select c1, t.c2.arr.size() from t t;

C1    T.C2.ARR.SIZE()   
 1                  3 
 2                  5