How to handle big data json having more than 32767 keys

Viewed 50

I am struggling to parse a JSON having more than 32767 keys The format of json is shown below. The idea is to fetch all the keys and value into a temp table , but due to length limitation unable to do so

{"1335":"345435sd","8989SD":"jddk8","dDDSF","87868658"......}

declare
 j  JSON_OBJECT_T;
 i  NUMBER;
 k  JSON_KEY_LIST;
 arr JSON_ARRAY_T;
 v_key varchar2(2000);
 v_value varchar2(2000);
 CURSOR c_json IS
 select  treat(col_clob as json) myJsonCol from t_clob; -- the data is stored as clob and it need to be 
begin
  FOR rec IN c_json
  LOOP
     j := JSON_OBJECT_T.parse(rec.myJsonCol);
     k := j.get_keys;
     FOR i in 1..k.COUNT
     LOOP
       dbms_output.put_line(k(i) || ' ' || j.get_String(k(i)));
        v_key :=k(i);
        v_value :=j.get_String(k(i));
       -- insert into temp(c1,c2) values(v_key,v_value);
     END LOOP;
   END LOOP;
END;
/ 

::Oracle Error: ORA-40684: maximum number of key names exceeded::

Reference - https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/oracle-json-restrictions.html#GUID-1DB81125-54A7-4CB6-864B-78E0E7E407C9 PL/SQL getter method JSON_OBJECT_T.get_keys() returns at most 32767 field names for a given JSON object. An error is raised if it is applied to an object with more than 32767 fields.

0 Answers
Related