By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE myjson (
doc CLOB,
CONSTRAINT json_documents_json_chk CHECK (doc IS JSON)
);
INSERT INTO myjson VALUES
('{
"totalResultsCount": 19,
"geonames":[
{
"geonameId":2593109,
"fcode": "ADM1"
},
{
"geonameId": 3336899,
"fcode": "ADM1"
}
]
}');
1 rows affected
DECLARE
l_str VARCHAR2(32767);
l_top_obj JSON_OBJECT_T;
l_dept_arr JSON_ARRAY_T;
l_dept_obj JSON_OBJECT_T;
l_emp_arr JSON_ARRAY_T;
l_emp_obj JSON_OBJECT_T;
BEGIN
SELECT j.doc
INTO l_str
FROM myjson j
WHERE j.doc.geonames.fcode LIKE '%ADM1%';
l_top_obj := JSON_OBJECT_T(l_str);
l_dept_arr := l_top_obj.get_array('geonames');
FOR i IN 0 .. l_dept_arr.get_size - 1
LOOP
l_dept_obj := TREAT(l_dept_arr.get(i) AS JSON_OBJECT_T);
DBMS_OUTPUT.PUT_LINE('index : '||i||' - geonameId : ' || l_dept_obj.get_number('geonameId'));
END LOOP;
END;
/
1 rows affected
dbms_output:
index : 0 - geonameId : 2593109
index : 1 - geonameId : 3336899