Get array size in oracle sql

Question

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 ?

What version of Oracle are you using?

Dai
– Dai

2020-07-29 09:37:58 +00:00
Commented Jul 29, 2020 at 9:37 — Dai
– Dai, Commented Jul 29, 2020 at 9:37
Oracle Database 19c Enterprise Edition Release 19.0

neo
– neo

2020-07-29 09:47:58 +00:00
Commented Jul 29, 2020 at 9:47 — neo
– neo, Commented Jul 29, 2020 at 9:47

Chris Saxon · Accepted Answer · 2020-07-29 10:30:36Z

3

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

answered Jul 29, 2020 at 10:30

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MT0 Over a year ago

What version of Oracle does this work on? Oracle 18 db<>fiddle does not work.

Chris Saxon Over a year ago

18c; you can use it with json_value ( c2, '$.arr.size()' ). Not sure what the issue with dot-notation is

Lokoba · Accepted Answer · 2020-07-29 11:13:16Z

For case with a blob field I see the following decision.

You have a table something like this

create table tmp_js
(
   id       number,
   document blob  
);

And table with json objects looks like this

ID    Document 
----  ----------
1     {"id": 1, "paramters": [{"t": 2}, {"t": 3}]}
2     {"id": 2, "paramters": [{"t": 2}, {"t": 3}, {"t": 4}]}
3     {"id": 3, "paramters": [{"t": 2}, {"t": 3}, {"t": 5}, {"t": 6}]}

Then you can resolve your task by following way

Step 1

You will get row's id and size of array in row's document.

select
    t.id,            
    (select
        count(*)
    from
        json_table(json_query(t.document, '$.paramters'), '$[*]' columns (value path '$.t'))
    ) as cnt       
from
    tmp_js t

Step2

You can make total summary from result of first step.

select
    count(*),
    sum(cnt)
from    
    (select
        t.id,            
        (select
            count(*)
        from
            json_table
            (
                json_query(t.document, '$.paramters'), 
                '$[*]' columns (value path '$.t')
            )
        ) as cnt       
    from
        tmp_js t)

Collectives™ on Stack Overflow

Get array size in oracle sql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related