Parse array of string in PLJSON oracle

Question

I have to parse a array of string in my JSON with ORACLE dans PLJSON module. I'm able to parse data without array like CLE,X,Y.. But when I want to get COLORS or REGROUPEMENT_COLORS, this return me nothing.

There is the code:

DECLARE
    obj json := json();
    obj_1 json := json();
    arr json_list := json_list();
    test varchar2(255);

BEGIN

    obj := json('{
  "DASHBOARD": {
    "userUid": "",
    "DATA_DASHBOARD": [
      {
        "CLE": "TESTTEST",
        "X": "",
        "Y": "",
        "COL": "",
        "ROW": "",
        "CLASSCOLOR": "",
        "COLORS": ["df","df"],
        "REGROUPEMENT_ID": "",
        "REGROUPEMENT_TEXT": "",
        "REGROUPEMENT_CLASSCOLOR": "",
        "REGROUPEMENT_X": "",
        "REGROUPEMENT_Y": "",
        "REGROUPEMENT_COL": "",
        "REGROUPEMENT_ROW": "",
        "REGROUPEMENT_COLORS": ["d","df"]
      }
    ]
  }
}');


    obj_1 :=json(obj.get('DASHBOARD'));


    arr := json_list(obj_1.get('DATA_DASHBOARD'));

     test := json_ext.get_string(json(arr.get(1)), 'REGROUPEMENT_COLORS');

    DBMS(test);


END;

Thanks !

Answer 1

REGROUPEMENT_COLORS is an array, not a string, so json_ext.get_string() is not returning anything. If you want a string then get REGROUPEMENT_COLORS[1] or REGROUPEMENT_COLORS[2]; if you want the array then use json_ext.get_json_list rather than json_ext.get_string.

To output a string use DBMS_OUTPUT.PUT_LINE not DBMS.

If you are using json_ext then you can use the full path.

PL/SQL:

DECLARE
  obj pljson := pljson(
    '{
      "DASHBOARD": {
        "userUid": "",
        "DATA_DASHBOARD": [
          {
            "CLE": "TESTTEST",
            "X": "",
            "Y": "",
            "COL": "",
            "ROW": "",
            "CLASSCOLOR": "",
            "COLORS": ["df","df"],
            "REGROUPEMENT_ID": "",
            "REGROUPEMENT_TEXT": "",
            "REGROUPEMENT_CLASSCOLOR": "",
            "REGROUPEMENT_X": "",
            "REGROUPEMENT_Y": "",
            "REGROUPEMENT_COL": "",
            "REGROUPEMENT_ROW": "",
            "REGROUPEMENT_COLORS": ["d","df"]
          }
        ]
      }
    }'
  );
  test varchar2(255);
  arr  pljson_list;
BEGIN
  test := pljson_ext.get_string( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS[1]');
  DBMS_OUTPUT.PUT_LINE(test);
  arr := pljson_ext.get_json_list( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS');
  arr.print;
  FOR i IN 1 .. arr.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( arr.get_string(i) );
  END LOOP;
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:

d
["d", "df"]
d
df

db<>fiddle here