How to get the number of elements in a JSON array stored as CLOB with Oracle 12c?
You can use JSON_TABLE:
SELECT id, var1, count(sub_id) subvaluesFROM JSON_TABLE ( to_clob('{ id: 123, var1: "abc", subvalues : [{ id: 1, value: "a", }, { id: 2, value: "b" } ]}'), '$' COLUMNS ( id NUMBER PATH '$.id', var1 VARCHAR PATH '$.var1', NESTED PATH '$.subvalues[*]' COLUMNS ( sub_id NUMBER PATH '$.id' ) ) )GROUP BY id, var1