Oracle将所有键/值转换为行

Question

我有一个简单的JSON对象{“a”：{“key1”：“value1”，“key2”：“value2”，“key3”：“value3”}，我想用一列“Key”将其转换成行。问题是我事先不知道钥匙的名字，或者有多少。换句话说，可能有“键4”、“键5”。也可能有“xyz1”：“xyz1”作为“a”对象中的键/值。我可以用JavaScript迭代这个JSON对象，但我这里的任务是，假设这个JSON对象作为CLOB存储在Oracle数据库中，如何将所有键转换成一个表？还是有可能。我考虑过可能使用JSON_表，但我必须指定模式和每个列的JSON路径，我无法提前指定。如果这是一个数组，并且每个元素模式都是固定的，那么JSON_表似乎就是为此而构建的。但我没有这样的JSON对象。只有一个像前面描述的那样。思想？

Answer 1

我不相信有一个SQL函数可以获取与PL/SQL中的JSON_OBJECT_t.get_keys函数类似的键名。一个选项是使用管道表函数解析JSON，您可以获得父键、子键和子键的值。

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;
/

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