CODEWITHSUNDEEP
sqljsonoracle-database
Menja
Menja

Reputation: 39

How to convert json to columns and rows with SQL?

i have a database with a JSON-Objekt in a column {"key_1":"value_1","key_2":"value_2",…} How can i get a table in this form:

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

.

Upvotes: 0

Views: 408

Answers (1)

0xdb
0xdb

Reputation: 3697

PL/SQL Object Types for JSON are simple to use. Try the following reproducible example:

create table jsondoc (id int, doc clob check (doc is json))
/
insert into jsondoc
select 1, '{"key1":"value1","key2":"value2","key3":"value3"}' from dual
/
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;
/

The query and it's outcomes:

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

Upvotes: 2

Related Questions