ORACLE: The best way to parse JSON into collection of object types-CodePudding

I have an object type: create type t_ref_rec is object(id number, name varchar2(256));

And a table type: create type t_ref_tbl is table of t_ref_rec;

I parse JSON onto a collection like this:

declare
    var_ref_tbl t_ref_tbl;
begin
    ...
    select
        t_ref_rec(id, name)
    bulk collect into
        var_ref_tbl
    from
        json_table
        (
            '[{"id":1, "name":"one"}, {"id":2, "name":"two"}]',
            '$[*]'
            columns
            (
                id number path '$.id',
                name varchar2 path '$.name'
            )
        );
    ....
end;

Is there a better way to parse data from JSON into a collection of t_ref_tbl type?

CodePudding user response：

If you're on 19c or higher, the best way is to state your object as the return type for a JSON_value call and the database will map it for you:

create type t_ref_rec is object(id number, name varchar2(256));
/
create type t_ref_tbl is table of t_ref_rec;
/

select json_value (
  '[{"id":1, "name":"one"}, {"id":2, "name":"two"}]',
  '$' returning t_ref_tbl 
) obj
from   dual;

OBJ(ID, NAME)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------
T_REF_TBL(T_REF_REC(1, 'one'), T_REF_REC(2, 'two'))

The on mismatch clause enable you to define what happens if there's a difference between the JSON document and the object type.