oracle - APEX_JSON 获取数组对象
问题描述
declare
l_json_doc VARCHAR2(32767);
l_numcols number;
l_numrows number;
begin
l_json_doc := '{
"table": {
"name": "sometablename",
"numofcolumns": 5,
"numofrows": 5,
"colheadings": [{
"colname": "customcol1",
"coltype": "number"
},
{
"colname": "customcol2",
"coltype": "varchar2"
},
{
"colname": "customcol3",
"coltype": "varchar2"
},
{
"colname": "customcol4",
"coltype": "varchar2"
},
{
"colname": "customcol5",
"coltype": "number"
}
],
"data": [{
"customcol1": "datacolumn1",
"customcol2": "datacolumn2",
"customcol3": "datacolumn3",
"customcol4": "datacolumn4",
"customcol5": "datacolumn5"
},
{
"customcol1": "2datacolumn1",
"customcol2": "2datacolumn2",
"customcol3": "2datacolumn3",
"customcol4": "2datacolumn4",
"customcol5": "2datacolumn5"
},
{
"customcol1": "3datacolumn1",
"customcol2": "3datacolumn2",
"customcol3": "3datacolumn3",
"customcol4": "3datacolumn4",
"customcol5": "3datacolumn5"
},
{
"customcol1": "4datacolumn1",
"customcol2": "4datacolumn2",
"customcol3": "4datacolumn3",
"customcol4": "4datacolumn4",
"customcol5": "4datacolumn5"
}
]
}
}';
APEX_JSON.parse(l_json_doc);
l_numcols := APEX_JSON.get_count(p_path => 'table.colheadings');
l_numrows := APEX_JSON.get_count(p_path => 'table.data');
FOR i IN 1 .. l_numrows LOOP
FOR j IN 1 .. l_numcols LOOP
dbms_output.put_line('TEST ' || APEX_JSON.get_varchar2(p_path => 'table.data[%d]') );
END LOOP;
END LOOP;
end;
这是我应该提取数据数组对象的代码。我期望以下输出:
TEST {"customcol1": "datacolumn1","customcol2": "datacolumn2","customcol3": "datacolumn3","customcol4": "datacolumn4","customcol5": "datacolumn5"}
TEST { "customcol1": "2datacolumn1","customcol2": "2datacolumn2","customcol3": "2datacolumn3","customcol4": "2datacolumn4","customcol5": "2datacolumn5"}
ETC ...
但是当我尝试使用 APEX_JSON.get_varchar2 从数据数组中获取 json 对象时,它返回空
解决方案
那里的代码有两个问题
- 您尚未提供该
get_varchar2
函数的索引。所以路径不完整。您需要在该p0
函数的参数中提供一个值 - 这不是如何
APEX_JSON.get_varchar2
工作的。您期望该函数返回数据数组中的整个 JSON 对象,但get_varchar2
不能这样做。它只能为您提供指定路径上的 VARCHAR值。它无法让您获得整个对象。
对于您的输出
DECLARE
l_json_doc VARCHAR2 (32767);
l_numcols NUMBER;
l_numrows NUMBER;
v_colname VARCHAR2 (32767);
BEGIN
l_json_doc := '{
"table": {
"name": "sometablename",
"numofcolumns": 5,
"numofrows": 5,
"colheadings": [{
"colname": "customcol1",
"coltype": "number"
},
{
"colname": "customcol2",
"coltype": "varchar2"
},
{
"colname": "customcol3",
"coltype": "varchar2"
},
{
"colname": "customcol4",
"coltype": "varchar2"
},
{
"colname": "customcol5",
"coltype": "number"
}
],
"data": [{
"customcol1": "datacolumn1",
"customcol2": "datacolumn2",
"customcol3": "datacolumn3",
"customcol4": "datacolumn4",
"customcol5": "datacolumn5"
},
{
"customcol1": "2datacolumn1",
"customcol2": "2datacolumn2",
"customcol3": "2datacolumn3",
"customcol4": "2datacolumn4",
"customcol5": "2datacolumn5"
},
{
"customcol1": "3datacolumn1",
"customcol2": "3datacolumn2",
"customcol3": "3datacolumn3",
"customcol4": "3datacolumn4",
"customcol5": "3datacolumn5"
},
{
"customcol1": "4datacolumn1",
"customcol2": "4datacolumn2",
"customcol3": "4datacolumn3",
"customcol4": "4datacolumn4",
"customcol5": "4datacolumn5"
}
]
}
}';
APEX_JSON.parse (l_json_doc);
l_numcols := APEX_JSON.get_count (p_path => 'table.colheadings');
l_numrows := APEX_JSON.get_count (p_path => 'table.data');
FOR i IN 1 .. l_numrows
LOOP
DBMS_OUTPUT.put ('TEST {');
FOR j IN 1 .. l_numcols
LOOP
v_colname :=
apex_json.get_varchar2 ('table.colheadings[%d].colname', j);
DBMS_OUTPUT.put (
'"' || v_colname || '":"'
|| APEX_JSON.get_varchar2 (
p_path => 'table.data[%d].' || v_colname,
p0 => i)
|| '",');
END LOOP;
DBMS_OUTPUT.put_line ('}');
END LOOP;
END;
这是代码的输出:
TEST {"customcol1":"datacolumn1","customcol2":"datacolumn2","customcol3":"datacolumn3","customcol4":"datacolumn4","customcol5":"datacolumn5",}
TEST {"customcol1":"2datacolumn1","customcol2":"2datacolumn2","customcol3":"2datacolumn3","customcol4":"2datacolumn4","customcol5":"2datacolumn5",}
TEST {"customcol1":"3datacolumn1","customcol2":"3datacolumn2","customcol3":"3datacolumn3","customcol4":"3datacolumn4","customcol5":"3datacolumn5",}
TEST {"customcol1":"4datacolumn1","customcol2":"4datacolumn2","customcol3":"4datacolumn3","customcol4":"4datacolumn4","customcol5":"4datacolumn5",}
注意:我不在乎删除每行中最后一个键:值对之后的最后一个逗号。如果需要,则必须将所有键值对存储在变量中,然后RTRIM
将逗号输出。
推荐阅读
- django - 使用关键字参数 '{'id_operation': ''}' 反转 'operation_info'
- r - 使用R如何删除特定字符后的所有单词
- python - 在熊猫的不同列中查找相等的值
- c++ - 如何检测 << 运算符链的结尾
- python - 如何向 stevedore ExtensionManager 添加新的扩展
- c - 位平行加权 Levenshtein 距离
- skiasharp - DrawText 在 Windows 中工作,在 Linux 中为空(Synology armv7l)
- html - 通过 R 笔记本渲染 kable 对象时的白色字体
- python - Python、Django:原始 sql 查询的替代方案
- flutter - Flutter中如何实现多层循环ListView?