CODEWITHSUNDEEP
oracle-databaseplsqloracle11gpljson
DEVLOGIN
DEVLOGIN

Reputation: 87

Parse JSON LIST in a JSON LIST PLSQL

I'm working with PLJSON and ORACLE ,I have a JSON like this 

{
  "DATA": {
    "FROM_DATA": [
      {
        "COL": "",
        "ROW": "",
        "GET_DATA": [{"0":"df"}],
        "SAVE_DATE": [{"2":"ar"}]
      }
    ]
  }
}

I'm able to parse FROM_DATA and get COL and ROW value but I can't parse GET_DATA and SAVE_DATA array.

This is my code :

objet_json :=json(i_json.get('DATA'));  

 JSONDATA:= json_list(objet_json.get('FROM_DATA'));  

JSONGETDATA:= json_list(objet_json .get('GET_DATA'));  (1)
 JSONSAVEDATA:= json_list(objet_json .get('SAVE_DATE'));  (2)

But I got an error on (1) and (2) : 

ora-30625 method dispatch on null self argument is disallowed

Upvotes: 0

Views: 4231

Answers (2)

MT0
MT0

Reputation: 167774

DATA.FROM_DATA is an array containing a single element; it is not an object.

Looking at the examples in the PLJSON git repository, you need to use .get(1) to get the first element of the array:

DECLARE
  i_json pljson := pljson(
    '{
      "DATA": {
        "FROM_DATA": [
          {
            "COL": "",
            "ROW": "",
            "GET_DATA": [{"0":"df"}],
            "SAVE_DATE": [{"2":"ar"}]
          }
        ]
      }
    }'
  );
  objet_json   pljson;
  JSONARRAY    pljson_list;
  JSONDATA     pljson;
  JSONGETDATA  pljson_list;
  JSONSAVEDATA pljson_list;
BEGIN
  objet_json   := pljson(i_json.get('DATA'));  
  JSONARRAY    := pljson_list(objet_json.get('FROM_DATA'));  
  JSONDATA     := pljson(JSONARRAY.get(1));
  JSONGETDATA  := pljson_list(JSONDATA.get('GET_DATA'));
  JSONSAVEDATA := pljson_list(JSONDATA.get('SAVE_DATE'));

  JSONGETDATA.print;
  JSONSAVEDATA.print;
END;
/

(Note: the objects/packages have the pl prefix as db<>fiddle does not allow creating synonyms; you should be able to remove those prefixes if your implementation has the appropriate synonyms created.)

outputs:

[{
  "0" : "df"
}]
[{
  "2" : "ar"
}]

db<>fiddle here

Upvotes: 0

Radagast81
Radagast81

Reputation: 3006

I think you have to loop over the elements:

objet_json := json(i_json.get('DATA'));  
JSONDATA   := json_list(objet_json.get('FROM_DATA'));  

FOR i IN JSONDATA.FIRST..JSONDATA.LAST LOOP
  JSONGETDATA  := json_list(JSONDATA(i).get('GET_DATA'));
  JSONSAVEDATA := json_list(JSONDATA(i).get('SAVE_DATE'));
END LOOP;

Perhaps you have to add some additional constructor calls:

objet_json := json(i_json.get('DATA'));  
JSONDATA   := json_list(objet_json.get('FROM_DATA'));  

FOR i IN JSONDATA.FIRST..JSONDATA.LAST LOOP
  JSONGETDATA  := json_list(json(JSONDATA(i)).get('GET_DATA'));
  JSONSAVEDATA := json_list(json(JSONDATA(i)).get('SAVE_DATE'));
END LOOP;

Upvotes: 0

Related Questions