CODEWITHSUNDEEP
jsonoracle-database
Ronald Burton
Ronald Burton

Reputation: 15

Oracle turning all keys/values to rows

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?

Upvotes: 1

Views: 1948

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6084

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

Upvotes: 2

Related Questions