How to get Key Value as resultset from Oracle JSON column using JSON_TABLE
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;/
Your JSON value is a single tuple, so you could use UNPIVOT to turn it into a table of key/value pairs:
with mydata as ( select '{ "a":9.0847, "b":859.947 }' myjsoncol from dual), q as ( select json_value(mydata.myjsoncol, '$.a') ca ,json_value(mydata.myjsoncol, '$.b') cb from mydata) select * from qunpivot (val for key in (ca as 'a', cb as 'b'));KEY VAL=== =======a 9.0847b 859.947Live SQL: https://livesql.oracle.com/apex/livesql/s/d31n9re90y6cpghi4i3m9hfoh
For Oracle 11g version Json manupulation is not supported. So we must use basic functions : SUBSTR / INSTR / SUBSTR
Check solution on another thread : Manipulating JSON data with SQL in Oracle