Oracle turning all keys/values to rows

Question

I have a simple JSON object { "A" : {"key1":"value1", "key2":"value2", "key3":"value3"} } that I want to turn into rows with a single column "Key". The problem is that I don't know ahead of time what the name of the key is, or how many there are. In other words there could be "key4", "key5", . . . And there could also be "xyz1":"XYZ1" as a key/value in the "A" object. I can iterate through this JSON object with JavaScript but my task here is, assuming that the is a JSON object stored in an Oracle database as a CLOB, how do I turn all of the keys into a table? Or is it possible. I looked at possibly using JSON_TABLE but I have to specify the schema and a json path to each column which I cannot specify ahead of time. If this was an array and each elements schema is fixed, it seems that JSON_TABLE is built for that. But I don't have such a JSON object. Only one like described earlier. Ideas?

I thought you wanted the values, but now I'm not sue if you actually want the key names, or both. Please edit your question to show the expected output from your sample JSON. — Alex Poole

EJ Egyed EJ Egyed · Accepted Answer · 2020-10-14T18:14:44

I don't believe there is a SQL function to get the names of the keys similar to the JSON_OBJECT_T.GET_KEYS function in PL/SQL. One option is to use a pipelined table function to parse the JSON and you can get the parent key, child keys, and value of the child keys.

Setup

CREATE TABLE sample_json
AS
    SELECT EMPTY_CLOB () || '{ "A" : {"key1":"value1", "key2":"value2", "key3":"value3"} }'    AS json_text
      FROM DUAL
    UNION ALL
    SELECT EMPTY_CLOB () || '{ "B" : {"xyz1":"XYZ1"} }' AS json_text FROM DUAL
    union all
    SELECT EMPTY_CLOB () || '{ "C" : {"somekey":"someval","weird":"strange"} }' AS json_text FROM DUAL;

CREATE TYPE json_rec_t AS OBJECT
(
    parent VARCHAR2 (50),
    jsonkey VARCHAR2 (50),
    jsonvalue VARCHAR2 (50)
);
/

CREATE TYPE json_tab_t IS TABLE OF json_rec_t;
/

CREATE OR REPLACE FUNCTION get_json_vals (p_json CLOB)
    RETURN json_tab_t
    PIPELINED
AS
    l_json          json_object_t := json_object_t (p_json);
    l_parent_keys   json_key_list;
    l_child         json_object_t;
    l_child_keys    json_key_list;
BEGIN
    l_parent_keys := l_json.get_keys;

    FOR i IN 1 .. l_parent_keys.COUNT
    LOOP
        l_child := l_json.get_object (l_parent_keys (i));

        l_child_keys := l_child.get_keys;

        FOR j IN 1 .. l_child_keys.COUNT
        LOOP
            PIPE ROW (json_rec_t (l_parent_keys (i), l_child_keys(j), l_child.get_string(l_child_keys(j))));
        END LOOP;
    END LOOP;

    RETURN;
END;
/

Query

SELECT * FROM sample_json,get_json_vals(json_text);

                                                       JSON_TEXT    PARENT    JSONKEY    JSONVALUE
________________________________________________________________ _________ __________ ____________
{ "A" : {"key1":"value1", "key2":"value2", "key3":"value3"} }    A         key1       value1
{ "A" : {"key1":"value1", "key2":"value2", "key3":"value3"} }    A         key2       value2
{ "A" : {"key1":"value1", "key2":"value2", "key3":"value3"} }    A         key3       value3
{ "B" : {"xyz1":"XYZ1"} }                                        B         xyz1       XYZ1
{ "C" : {"somekey":"someval","weird":"strange"} }                C         somekey    someval
{ "C" : {"somekey":"someval","weird":"strange"} }                C         weird      strange

Oracle turning all keys/values to rows

1 Answers

Setup

Query