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

Question

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?

Answer 1

You can create the types and function:

CREATE TYPE key_value_pair IS OBJECT(
  key   VARCHAR2(4000),
  value VARCHAR2(4000)
);

CREATE TYPE key_value_list IS TABLE OF key_value_pair;

CREATE FUNCTION get_key_values(
  json IN CLOB
) RETURN key_value_list PIPELINED
IS
  doc_keys JSON_KEY_LIST;
  obj      JSON_OBJECT_T := JSON_OBJECT_T.PARSE ( json );
BEGIN
  doc_keys := obj.GET_KEYS;
  FOR i IN 1 .. doc_keys.COUNT LOOP
    PIPE ROW ( key_value_pair( doc_keys(i), obj.get_string(doc_keys(i) ) ) );
  END LOOP;
END get_key_values;
/

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

SELECT t.id,
       kv.key,
       kv.value
FROM   table_name t
       CROSS APPLY get_key_values(t.json_value) kv;

Which outputs:

ID	KEY	VALUE
1	max. temp.	40 F
1	I.D.	1/3 in
2	a	8
2	b	27
3	"a": 8	2,3,4
3	a:b	a,b,c

---

Alternative solution

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" }')

Also outputs:

ID	KEY	VALUE
1	max. temp.	40 F
1	I.D.	1/3 in
2	a	8
2	b	27
3	"a": 8	2,3,4
3	a:b	a,b,c

---

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