Dynamic Json into a table format

hsemar

Hi,

I have  working on some dynamic representation of json (Generated by a rest webservice call) in the form of a table [In oracle apex to represent in the form a table].  My Webservice call return dynamic column names, how to represent in the form of a table through SQL or PLSQL.  Is this possible.

All examples are shown if we know the key values in the json.   But i'm not aware of those keys, what ever keys that generated by webservice call, those should be my column names.

e.g.,

If My webservice call generates 

{

name:  "ramesh"

age:  30

}

Some times, the webservice call might generate more columns or a different column names.

{

name" "ramesh"

dept : "ECE"

title: "Sr.

lastname: "gopi"

}

How do i represent this in the webservice call if i have a dynamic column names through apex_json.  I am trying to implement using apex_json but not successful.  Any suggestions..

Regards,

Ramesh G

Solomon Yakobson

It is not clear what is your question. You have json document of unknown structure. Parsing it out and getting tag names and their values isn't an issue. However, in general json maps into set of tables (e.g. customers, suppliers, products, orders... are all part of json document). And even if your json is simple enough to map into a single table how can you save it into relational table if you don't know json structure upfront? The only way I can see is having value pair table.

SY.

Solomon Yakobson

It is not clear what is your question. You have json document of unknown structure. Parsing it out and getting tag names and their values isn't an issue. However, in general json maps into set of tables (e.g. customers, suppliers, products, orders... are all part of json document). And even if your json is simple enough to map into a single table how can you save it into relational table if you don't know json structure upfront? The only way I can see is having value pair table.

SY.

hsemar

Representing them in the form of a table is enough based on the provided json (Consider Simple json).  I'm able to get the json into the clob collection.   And also, able to figure out the tag names.  But, i'm not able to represent the json clob collection in the form a table with the respective tag names.

Regards,

Ramesh G

AndrewSayer

hsemar wrote:Hi,I have working on some dynamic representation of json (Generated by a rest webservice call) in the form of a table [In oracle apex to represent in the form a table]. My Webservice call return dynamic column names, how to represent in the form of a table through SQL or PLSQL. Is this possible.All examples are shown if we know the key values in the json. But i'm not aware of those keys, what ever keys that generated by webservice call, those should be my column names.e.g.,If My webservice call generates {name: "ramesh"age: 30}Some times, the webservice call might generate more columns or a different column names.{name" "ramesh"dept : "ECE"title: "Sr.lastname: "gopi"}How do i represent this in the webservice call if i have a dynamic column names through apex_json. I am trying to implement using apex_json but not successful. Any suggestions.. Regards,Ramesh G

You should be asking in the   space, there’s built in functionality for what you seem to want to do. It will obviously be version dependent so don’t forget to include that (Hint: APEX 19 has a lot of extra functionality that you probably want)

Rithu Raj

@hsemar Did you do it finally? I have a similar requirement of parsing dynamic nested Json into Oracle table. Please write me if you have done it.