Hi,
I am trying to extract the fields and the values of these fields from a JSON string with a PL/SQL piece of code:
set serveroutput on
declare
l_json_text varchar2(32767);
l_count pls_integer;
l_members wwv_flow_t_varchar2;
l_paths apex_t_varchar2;
l_exists boolean;
begin
---
l_json_text := '{
"items":[
{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1983-01-11T23:00:00Z","sal":1100,"comm":null,"deptno":20},
{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"1981-06-08T22:00:00Z","sal":2450,"comm":null,"deptno":10},
{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"1982-01-22T23:00:00Z","sal":1300,"comm":null,"deptno":10}
]}';
---
apex_json.parse(l_json_text);
---
l_count := APEX_JSON.get_count(p_path => 'items');
dbms_output.put_line('Members count: ' || l_count);
---
for i in 1 .. l_count
loop
dbms_output.put_line('Employee Number: ' ||
-- apex_json.get_number(p_path => 'items.empno[%d].empno', p0 => i));
-- apex_json.get_number(p_path => 'items.empno[%d]', p0 => i));
apex_json.get_number(p_path => 'items.empno.empno[%d]', p0 => i));
end loop;
end;
/
I can get the number of elements of the type "empno", but I am unable to get the values for "empno", "ename",...
Does someone know how to extract these values?
Kind Regards