Convert JSON to table when all JSON field keys are unknown/arbitrary/random

There is a table in Oracle that has huge number of rows with JSON objects which have unknown/arbitrary/random field keys!

Basically those JSON objects are industrial products specifications. There are over 20k categories and 2M products. The JSON looks like {"max. temp.": "40 F", "I.D.": "1/3 in", ...}

How to convert JSON like { "a": 8, "b":27 } to a table like:

key | val
--- | ---
a   | 8
b   | 27

in case when all JSON field keys are unknown/arbitrary/random?

You can create the function:

CREATE FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
/

Then, from Oracle 12, you can use the query:

SELECT t.id,
       get_key(j.idx, t.json_value) AS key,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.json_value,
         '$.*'
         COLUMNS (
           idx FOR ORDINALITY,
           value VARCHAR2(4000) PATH '$'
         )
       ) j

Which, for the sample data:

CREATE TABLE table_name (id NUMBER, json_value CLOB CHECK (json_value IS JSON));
INSERT INTO table_name (id, json_value) VALUES (1, '{"max. temp.": "40 F", "I.D.": "1/3 in"}');
INSERT INTO table_name (id, json_value) VALUES (2, '{ "a": 8, "b":27 }');
INSERT INTO table_name (id, json_value) VALUES (3, '{ "\"a\": 8": "2,3,4", "a:b":"a,b,c" }')

Outputs:

In later Oracle versions, you can declare the function as part of the query:

WITH FUNCTION get_key(
  pos  IN PLS_INTEGER,
  json IN CLOB
) RETURN VARCHAR2 
AS
  doc_keys JSON_KEY_LIST;
BEGIN
  doc_keys := JSON_OBJECT_T.PARSE ( json ).GET_KEYS;
  RETURN doc_keys( pos );
END get_key;
SELECT t.id,
       get_key(j.idx, t.json_value) AS key,
       j.value
FROM   table_name t
       CROSS APPLY JSON_TABLE(
         t.json_value,
         '$.*'
         COLUMNS (
           idx FOR ORDINALITY,
           value VARCHAR2(4000) PATH '$'
         )
       ) j

Which outputs the same.

fiddle