Forum Stats

  • 3,838,560 Users
  • 2,262,383 Discussions
  • 7,900,687 Comments

Discussions

Dynamic Json into a table format

hsemar
hsemar Member Posts: 117 Blue Ribbon
edited Jun 14, 2019 3:50PM in SQL & PL/SQL

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

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Jun 10, 2019 1:29PM Answer ✓

    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

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,594 Red Diamond
    edited Jun 10, 2019 1:29PM Answer ✓

    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
  • hsemar
    hsemar Member Posts: 117 Blue Ribbon
    edited Jun 10, 2019 1:56PM

    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
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jun 10, 2019 1:58PM
    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)

    hsemar
  • Rithu Raj
    Rithu Raj Member Posts: 42 Blue Ribbon

    @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.