Skip to Main Content

Oracle Database Express Edition (XE)

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!

Get Value of Node Name in json Nested PATH

3857119Feb 17 2019 — edited Feb 19 2019

Hi,

I am working in -- Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

I have the following:

WITH json AS (

SELECT '{"date":"2019-02-14"

    ,"data":{"AAPL":{"open":"169.71","close":"170.80","high":"171.26","low":"169.38","volume":"21175371"}

           ,"MSFT":{"open":"106.31","close":"106.90","high":"107.29","low":"105.66","volume":"21621821"}}}' doc 

FROM dual

)

SELECT jt1.*

FROM

json j,

json_table(j.doc, '$'

   COLUMNS

   CLOSE\_DATE varchar2(200) PATH '$.date'

 , NESTED  PATH '$.data\[\*\]'  COLUMNS (

   ticker varchar2(2000) PATH '$.\*',

   NESTED PATH '$.\*' COLUMNS

                 (                      

                    opn   VARCHAR2(2000) PATH '$.open',

                    clse  VARCHAR2(2000) PATH '$.close',

                    hgh   VARCHAR2(2000) PATH '$.high',

                    lows  VARCHAR2(2000) PATH '$.low',

                    volu  VARCHAR2(2000) PATH '$.volume'

                 )                  

                )

               ) jt1;

It returns

pastedImage_0.png

How do I get the two Stock Symbols?

Thanks

This post has been answered by Gaz in Oz on Feb 17 2019
Jump to Answer
Comments
Post Details
Added on Feb 17 2019
7 comments
794 views