How to get Key Value as resultset from Oracle JSON

问题:

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.

回答1:

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 q
unpivot (val for key in (ca as 'a', cb as 'b'));

KEY VAL
=== =======
a   9.0847
b   859.947

Live SQL: https://livesql.oracle.com/apex/livesql/s/d31n9re90y6cpghi4i3m9hfoh

回答2:

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