Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using JSON to parse multiple values back to SQL?

steffiJun 30 2020 — edited Jul 2 2020

This is a general question as to whether JSON is a typical solution for when you want to parse multiple values from a function called from SQL?

so if I have a function that enriches data

select fielda, fieldb, fieldc

json_value(some_enriched_values, '$.enrichmenta') as enriched_fielda,

json_value(some_enriched_values,'$.enrichmentb') as enriched_fieldb,

json_value(some_enriched_values,'$.enrichmentc') as enriched_fieldc

from (select fielda, fieldb, fields, get_enriched_values(fielda, fieldb, fieldc) as some_enriched_values from some_table)

function get_enriched_values(fielda varchar2, fieldb varchar2, fieldc varchar2)

return varchar2

is

return json_object('enrichmenta' value enrichmenta, 'enrichementb' value enrichmentb, 'enrichmentc' value enrichementc);

.....

What if any caveats are there to this approach?

Comments
Post Details
Added on Jun 30 2020
9 comments
1,422 views