Как получить данные в табличной форме из колонки с JSON объектом?

Question

Есть таблица с JSON объектом в одной из колонок:

create table jsondoc (id int, doc clob check (doc is json));

insert into jsondoc
select 1, '{"key1":"value1","key2":"value2","key3":"value3"}' from dual;

Как можно получить данные из этой колонки в табличной форме?

Ожидаю получить:

KEYS     VALUES
-----    -------
key_1    value_1
key_2    value_2
...      ...

_{Свободный перевод вопроса How to convert json to columns and rows with SQL? от участника @Menja}

Answer 1

Можно воспользоваться PL/SQL Object Types for JSON, они довольно просты в применении.

Воспроизводимый пример:

create or replace package pack as 
    type pair is record (key varchar2(8), value varchar2(16));
    type pairtab is table of pair;
    function getpairs return pairtab pipelined;
end;
/
create or replace package body pack as 
    function getpairs return pairtab pipelined is
        doc clob;
        obj json_object_t;
        keys json_key_list;
    begin
        select doc into doc from jsondoc;
        obj := json_object_t (doc);
        keys := obj.get_keys;
        for i in 1..keys.count loop
            pipe row (pair (keys(i), obj.get_string (keys(i))));
        end loop;
        return;
    end;
end;
/

Сам запрос и результат:

select * from pack.getpairs()
/
KEY      VALUE           
-------- ----------------
key1     value1          
key2     value2          
key3     value3

_{Свободный перевод ответа от участника @0xdb}

БЛОГ НА HUSL

Как получить данные в табличной форме из колонки с JSON объектом?

Ответы (1 шт):