Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table jsondoc (id int, doc clob check (doc is json))
insert into jsondoc
select 1, '{"key1":"value1","key2":"value2","key3":"value3"}' from dual
1 rows affected

create or replace package pack as 
    type pair is record (key varchar2(8), value varchar2(16));
    type pairtab is table of pair;
    function getpairs return pairtab pipelined;
end;
/
create or replace package body pack as 
    function getpairs return pairtab pipelined is
        doc clob;
        obj json_object_t;
        keys json_key_list;
    begin
        select doc into doc from jsondoc;
        obj := json_object_t (doc);
        keys := obj.get_keys;
        for i in 1..keys.count loop
            pipe row (pair (keys(i), obj.get_string (keys(i))));
        end loop;
        return;
    end;
end;
/
select * from pack.getpairs()
/   


KEY
VALUE


key1
value1

key2
value2

key3
value3