For example imagine the table (my_table) with close to a million rows with various json data with unknown json key names:

| JSON_COLUMN                       | DATE       |
| --------------------------------- | ---------- |
| {"a":1, "b":2, "c",3}             | 2021-01-01 |
| {"a":111}                         | 2021-01-01 |
| {"a":11, "b":222, "c",333, "d",4} | 2021-01-02 |
| ...                               | ...        |
| ...                               | ...        |
| ...                               | ...        |
| ...                               | ...        |

The output must be something like:

a
b
c
d
...
...

I am able to get the distinct JSON values for a know key by doing:

select distinct json_value(JSON_COLUMN, '$.a') from my_table;

but not sure how to do the same for the unknown JSON keys.

CodePudding user response：

You may use json_dataguide aggregate function to access the structure of JSON documents.

Then by filtering only object steps you'll get all the keys:

with a(json_column, dt) as (
  select '{"a":1, "b":2, "c":3}', date '2021-01-01' from dual union all
  select '{"a":1}', date '2021-01-01' from dual union all
  select '{"a":1, "b":2, "c":3, "d":4}', date '2021-01-02' from dual
)
select distinct
  substr(elems, 3) as key_name
from json_table(
  /*Aggregate the overall structure of all JSON values*/
  (select json_dataguide(json_column) from a),
  '$[*]'
  columns (
    elems varchar(10) path '$."o:path"'
  )
)
/*Only object steps*/
where elems like '$.%'

db<>fiddle

UPD: Please note, that there are typos in the original data: commas instead of colons in the first and the third rows, which make them invalid JSON documents. Oracle silently ignores invalid JSON documents and returns a and b as the result for the original data. I've fixed those typos in the query above.