How to get Key Value as resultset from Oracle JSON column using JSON_TABLE

Question

I have googled aplenty, and can't seem to find a simple solution to my simple use case. I have a json column in an Oracle 12C database (actually a varchar with json constraint of course), and in that column I store a representation of a Map like this

{
"a":9.0847,
"b":859.947
}

In plsql I would like to return a result set of that looks like this

key val
a   9.0847
b   859.947

I have tinkered with seemingly infinite variations of this below, and all the examples are too contrived for my use case.

select b.* from mytable a,json_table(myJsonCol,'$'
columns ( value varchar2(500) path '$.myjsonkey')) b

but this only returns a list of values, without the corresponding keys. The json data is ALWAYS string-double key vals.

thanks

EDIT To add a bit more context, i use json_each in postgres to do this now, and i'm looking for a similar method in Oracle.

Answer 1

Try this:

declare
  jo JSON_OBJECT_T;
  i  NUMBER;
  keys JSON_KEY_LIST;
  CURSOR c_json IS
     SELECT myJsonCol FROM mytable;
begin
  FOR rec IN c_json
  LOOP
     jo := JSON_OBJECT_T.parse(rec.myJsonCol);
     keys := jo.get_keys;
     dbms_output.put_line('KEY VAL');
     FOR i in 1..keys.COUNT
     LOOP
        dbms_output.put_line(keys(i) || ' ' || jo.get_Number(keys(i)));
     END LOOP;
   END LOOP;
END;
/