Search code examples
jsonoracleoracle12c

How to get the number of elements in a JSON array stored as CLOB with Oracle 12c?

I'm storing a java class A as A_DOC in a clob column in my database.

The structure of A is like:

{

id : 123

var1: abc

subvalues : [{

 id: 1 
 value : a

 },
{
id: 1

value :b
}

...

}
]}

I know I can do things like

select json_query(a.A_DOC, '$.subvalues.value') from table_name a;

and so on, but how I'm looking for a way to count the number of elements in the subvalues array through an sql query. Is this possible?

Solution

  • the function exists in Oracle 18 only

    SELECT json_query('[19, 15, [16,2,3]]','$[*].size()' WITH ARRAY WRAPPER)  FROM dual;

SELECT json_value('[19, 15, [16,2,3]]','$.size()')  FROM dual;