在 PLJSON oracle 中解析字符串数组 [英] Parse array of string in PLJSON oracle

问题描述

我必须使用 ORACLE dans PLJSON 模块解析 JSON 中的字符串数组.我能够在没有数组的情况下解析数据，例如 CLE,X,Y..但是当我想获得 COLORS 或 REGROUPEMENT_COLORS 时，这不会给我任何回报.

有代码:

DECLAREobj json := json();obj_1 json := json();arr json_list := json_list();测试 varchar2(255);开始对象 := json('{仪表盘": {"userUid": "",数据仪表板":[{"CLE": "TESTTEST",X": ""，"Y": "","COL": "",排": ""，"类色": "","颜色": ["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('仪表板'));arr := json_list(obj_1.get('DATA_DASHBOARD'));测试:= json_ext.get_string(json(arr.get(1)), 'REGROUPEMENT_COLORS');数据库管理系统(测试)；结尾;

谢谢！

解决方案

REGROUPEMENT_COLORS 是一个数组，而不是一个字符串，所以 json_ext.get_string() 没有返回任何东西.如果你想要一个字符串然后得到 REGROUPEMENT_COLORS[1] 或 REGROUPEMENT_COLORS[2];如果你想要数组，那么使用 json_ext.get_json_list 而不是 json_ext.get_string.

要输出字符串，请使用 DBMS_OUTPUT.PUT_LINE 而不是 DBMS.

如果您使用的是 json_ext，那么您可以使用完整路径.

PL/SQL:

DECLAREobj pljson := pljson('{仪表盘": {"userUid": "",数据仪表板":[{"CLE": "TESTTEST",X": ""，"Y": "","COL": "",排": ""，"类色": "","颜色": ["df","df"],"REGROUPEMENT_ID": "","REGROUPEMENT_TEXT": "","REGROUPEMENT_CLASSCOLOR": "","REGROUPEMENT_X": "","REGROUPEMENT_Y": "","REGROUPEMENT_COL": "","REGROUPEMENT_ROW": "","REGROUPEMENT_COLORS": ["d","df"]}]}}');测试 varchar2(255);arr pljson_list;开始测试 := pljson_ext.get_string( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS[1]');DBMS_OUTPUT.PUT_LINE(测试)；arr := pljson_ext.get_json_list( obj, 'DASHBOARD.DATA_DASHBOARD[1].REGROUPEMENT_COLORS');arr.print;FOR i IN 1 .. arr.COUNT 循环DBMS_OUTPUT.PUT_LINE(arr.get_string(i));结束循环；结尾;/

(注意:对象/包具有 pl 前缀，因为 db<>fiddle 不允许创建同义词；如果您的实现具有适当的同义词，您应该能够删除这些前缀已创建.)

输出:

<块引用><前>d["d", "df"]ddf

db<>fiddle 这里

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 !

解决方案

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

这篇关于在 PLJSON oracle 中解析字符串数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！