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.

@RaviShankar i never found one, had to keep track of keys by various object types and use that separately — user3059517
– user3059517, Commented Oct 4, 2018 at 21:06
How did you do that? Were you able to get all the keys in a single column? — Ravi Shankar
– Ravi Shankar, Commented Oct 5, 2018 at 4:25
@RaviShankar no, we ended up storing the keys in another table, but your idea of having a column to store them would probably accomplish the same.. problem is keeping them consistent — user3059517
– user3059517, Commented Oct 5, 2018 at 12:23
Possibly, You can use the terminology as given in the link: link — Popeye
– Popeye, Commented Jun 18, 2019 at 5:28

Lukas Eder · Accepted Answer · 2022-11-23 14:03:32Z

2

Here's a generic solution that I tried on 18c and 21c, which uses PL/SQL APIs from within a SQL WITH function to produce the desired output (you can, of course, also store the function):

with
  function json_keys(j varchar2) return clob as
    jo json_object_t;
    k json_key_list;
    r clob;
  begin
    jo := json_object_t(j);
    k := jo.get_keys();
    select coalesce(
      json_arrayagg(column_value returning clob),
      json_array(returning clob)
    )
    into r
    from table (k);
    return r;
  end;
select o, json_keys(o)
from (
  select '{}' as o from dual union all
  select '{"a":1}' from dual union all
  select '{"a":1,"b":2}' from dual
) t;

Resulting in:

|O            |JSON_KEYS(O)|
|-------------|------------|
|{}           |[]          |
|{"a":1}      |["a"]       |
|{"a":1,"b":2}|["a","b"]   |

edited Nov 23, 2022 at 14:03

answered Sep 30, 2022 at 14:00

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3059517 Over a year ago

Danke, welche Version von Oracle benutzt du? (what version of oracle are you using)

Lukas Eder Over a year ago

@MarkGiaconia: I tried this on 18c and 21c, not on 12c. There have been tons of more recent JSON improvements, so it might be that this doesn't yet work on 12c... Although, the json_object_t type is already described in 12c, so you might get a solution similar to this to work on your installation, too: docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/…

Saxon · Accepted Answer · 2020-11-01 14:04:43Z

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;
/

answered Nov 1, 2020 at 14:04

Saxon

9374 silver badges8 bronze badges

Comments

Jeffrey Kemp · Accepted Answer · 2016-11-06 14:01:42Z

0

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

edited Nov 6, 2016 at 14:01

answered Nov 6, 2016 at 13:53

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

1 Comment

user3059517 Over a year ago

thanks, but the problem is I can't know that a and b exist as keys....all the data has potentially different fields as the key in the map.

Equipe sitsApp · Accepted Answer · 2019-12-06 08:56:17Z

-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

answered Dec 6, 2019 at 8:56

Equipe sitsApp

1

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related