Oracle JSON_TABLE query for nested json data

OK,
so I have a json data set and I want to parse it as a table for loading into this table:
create table eq_data(eq_data_id varchar2(500),
                     entry_time timestamp(3),
                     entry_from date,
                     entry_to date,
                     data clob,
                     CONSTRAINT eq_json_ck CHECK (data IS JSON (STRICT))) tablespace eq;

/* Loading method (feed from website) */                    
insert into eq_data
  select *
    from (select sys_guid() eq_json_id,
                 systimestamp entry_time,
                 trunc(sysdate - 1) entry_from,
                 sysdate entry_to,
                 pk_quakeData.getUSGS_EqFeed(pFormat => 'geojson',
                                             pFrom   => trunc(sysdate),
                                             pTo     => trunc(sysdate) +
                                                        .5 / 24) data
            from dual)
   where data is json;

select * from eq_data;

/* query output */
   	EQ_DATA_ID	ENTRY_TIME	ENTRY_FROM	ENTRY_TO	DATA
1	88368686BF254066A49C6D4296E85F6D	14-MAY-15 08.58.10.264 PM	13-May-15	14-May-15 20:58:10	<CLOB>

/* THE DATA */
{"type":"FeatureCollection",
        "metadata":{"generated":1431614890000,
                    "url":"http://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2015-05-14T00:00:00&endtime=2015-05-14T00:30:00",
                    "title":"USGS Earthquakes",
                    "status":200,
                    "api":"1.0.17",
                    "count":4},
        "features":[
         	         {"type":"Feature",
                   	       "properties":{"mag":1.23,"place":"6km E of Santa Rosa, California","time":1431563327590,"updated":1431567965848,"tz":-420,"url":"http://earthquake.usgs.gov/earthquakes/eventpage/nc72448816","detail":"http://earthquake.usgs.gov/fdsnws/event/1/query?eventid=nc72448816&format=geojson","felt":null,"cdi":null,"mmi":null,"alert":null,"status":"reviewed","tsunami":0,"sig":23,"net":"nc","code":"72448816","ids":",nc72448816,","sources":",nc,","types":",cap,general-link,geoserve,nearby-cities,origin,phase-data,scitech-link,","nst":21,"dmin":0.04804,"rms":0.06,"gap":84,"magType":"md","type":"earthquake","title":"M 1.2 - 6km E of Santa Rosa, California"},
                           "geometry":{"type":"Point","coordinates":[-122.635,38.432,7.65]},
                           "id":"nc72448816"},
                   {"type":"Feature",
                           "properties":{"mag":0.93,"place":"6km S of Gilroy, California","time":1431563284840,"updated":1431570320460,"tz":-420,"url":"http://earthquake.usgs.gov/earthquakes/eventpage/nc72448811","detail":"http://earthquake.usgs.gov/fdsnws/event/1/query?eventid=nc72448811&format=geojson","felt":null,"cdi":null,"mmi":null,"alert":null,"status":"reviewed","tsunami":0,"sig":13,"net":"nc","code":"72448811","ids":",nc72448811,","sources":",nc,","types":",cap,general-link,geoserve,nearby-cities,origin,phase-data,","nst":19,"dmin":0.01702,"rms":0.08,"gap":43,"magType":"md","type":"earthquake","title":"M 0.9 - 6km S of Gilroy, California"},
                           "geometry":{"type":"Point","coordinates":[-121.5635,36.9511667,5.63]},
                           "id":"nc72448811"},
                   {"type":"Feature",
                           "properties":{"mag":0.53,"place":"1km N of The Geysers, California","time":1431562931830,"updated":1431563024200,"tz":-420,"url":"http://earthquake.usgs.gov/earthquakes/eventpage/nc72448806","detail":"http://earthquake.usgs.gov/fdsnws/event/1/query?eventid=nc72448806&format=geojson","felt":null,"cdi":null,"mmi":null,"alert":null,"status":"automatic","tsunami":0,"sig":4,"net":"nc","code":"72448806","ids":",nc72448806,","sources":",nc,","types":",general-link,geoserve,nearby-cities,origin,phase-data,","nst":7,"dmin":0.01931,"rms":0.03,"gap":75,"magType":"md","type":"earthquake","title":"M 0.5 - 1km N of The Geysers, California"},
                           "geometry":{"type":"Point","coordinates":[-122.7563324,38.7949982,0.04]},
                           "id":"nc72448806"},
                   {"type":"Feature",
                           "properties":{"mag":1.13,"place":"4km NNW of San Jacinto, California","time":1431562033340,"updated":1431612600509,"tz":-420,"url":"http://earthquake.usgs.gov/earthquakes/eventpage/ci37379952","detail":"http://earthquake.usgs.gov/fdsnws/event/1/query?eventid=ci37379952&format=geojson","felt":null,"cdi":null,"mmi":null,"alert":null,"status":"reviewed","tsunami":0,"sig":20,"net":"ci","code":"37379952","ids":",ci37379952,","sources":",ci,","types":",cap,general-link,geoserve,nearby-cities,origin,phase-data,scitech-link,","nst":25,"dmin":0.1156,"rms":0.18,"gap":71,"magType":"ml","type":"earthquake","title":"M 1.1 - 4km NNW of San Jacinto, California"},
                           "geometry":{"type":"Point","coordinates":[-116.9718333,33.8213333,12.71]},
                           "id":"ci37379952"}
                   ],
        "bbox":[-122.7563324,33.8213333,0.04,-116.9718333,38.7949982,12.71]
}

Open in new window


so far, this part of it is fine and does what I expect it to do ( I am translating the "properties" data):
select a.data.metadata.generated gen_date,
       a.data.metadata.url       url,
       a.data.metadata.title     title,
       a.data.metadata.status    status,
       a.data.metadata.api       api,
       a.data.metadata.count     eq_count,
       p.*
  from eq_data a,
       json_table(a.data,
                  '$.features[*].properties'
                  columns(mag number path '$.mag',
                          place varchar2(100 char) path '$.place',
                          time Integer path '$.time',
                          updated Integer path '$.updated',
                          tz Integer path '$.tz',
                          url varchar2(100 char) path '$.url',
                          detail varchar2(100 char) path '$.detail',
                          felt Integer path '$.felt',
                          cdi number path '$.cdi',
                          mmi number path '$.mmi',
                          alert varchar2(100 char) path '$.alert',
                          status varchar2(100 char) path '$.status',
                          tsunami Integer path '$.tsunami',
                          sig Integer path '$.sig',
                          net varchar2(100 char) path '$.net',
                          code varchar2(100 char) path '$.code',
                          ids varchar2(100 char) path '$.ids',
                          sources varchar2(100 char) path '$.sources',
                          types varchar2(100 char) path '$.types',
                          nst Integer path '$.nst',
                          dmin number path '$.dmin',
                          rms number path '$.rms',
                          gap number path '$.gap',
                          magType varchar2(100 char) path '$.magType',
                          type varchar2(100 char) path '$.type')) p;

/* OUTPUT */
   	MAG	PLACE	TIME	UPDATED	TZ	CDI	MMI	ALERT	TSUNAMI	SIG	NET	CODE	IDS	SOURCES	TYPES	NST	DMIN	RMS	GAP	MAGTYPE	TYPE
1	1.23	6km E of Santa Rosa, California	1431563327590	1431567965848	-420				0	23	nc	72448816	,nc72448816,	,nc,	,cap,general-link,geoserve,nearby-cities,origin,phase-data,scitech-link,	21	0.04804	0.06	84	md	earthquake
2	0.93	6km S of Gilroy, California	1431563284840	1431570320460	-420				0	13	nc	72448811	,nc72448811,	,nc,	,cap,general-link,geoserve,nearby-cities,origin,phase-data,	19	0.01702	0.08	43	md	earthquake
3	0.53	1km N of The Geysers, California	1431562931830	1431563024200	-420				0	4	nc	72448806	,nc72448806,	,nc,	,general-link,geoserve,nearby-cities,origin,phase-data,	7	0.01931	0.03	75	md	earthquake
4	1.13	4km NNW of San Jacinto, California	1431562033340	1431612600509	-420				0	20	ci	37379952	,ci37379952,	,ci,	,cap,general-link,geoserve,nearby-cities,origin,phase-data,scitech-link,	25	0.1156	0.18	71	ml	earthquake

Open in new window


The results are 4 rows of data, formatted as expected.  However, the most crucial part of this data set is the "geometry" set.
I have been trying to un-nest the data properly and I am beginning to think it isn't possible, or I am not understanding the documentation correctly (yes, I have been reading the docs, as well as others' notes on how to unnest json data).

Here is what I have tried:

select a.data.features.geometry, g.*
  from eq_data a,
       json_table(a.data,
                  '$.features[*]'
                  columns(dataset_type path '$.type',
                          nested path '$.geometry'
                          columns(geom_type path '$.type',
                                  coord_set path '$.coordinates'))) g;

Open in new window


The output is less than ideal...

   	FEATURES	DATASET_TYPE	GEOM_TYPE	COORD_SET
1	[{"type":"Point","coordinates":[-122.635,38.432,7.65]},{"type":"Point","coordinates":[-121.5635,36.9511667,5.63]},{"type":"Point","coordinates":[-122.7563324,38.7949982,0.04]},{"type":"Point","coordinates":[-116.9718333,33.8213333,12.71]}]	Feature	Point	
2	[{"type":"Point","coordinates":[-122.635,38.432,7.65]},{"type":"Point","coordinates":[-121.5635,36.9511667,5.63]},{"type":"Point","coordinates":[-122.7563324,38.7949982,0.04]},{"type":"Point","coordinates":[-116.9718333,33.8213333,12.71]}]	Feature	Point	
3	[{"type":"Point","coordinates":[-122.635,38.432,7.65]},{"type":"Point","coordinates":[-121.5635,36.9511667,5.63]},{"type":"Point","coordinates":[-122.7563324,38.7949982,0.04]},{"type":"Point","coordinates":[-116.9718333,33.8213333,12.71]}]	Feature	Point	
4	[{"type":"Point","coordinates":[-122.635,38.432,7.65]},{"type":"Point","coordinates":[-121.5635,36.9511667,5.63]},{"type":"Point","coordinates":[-122.7563324,38.7949982,0.04]},{"type":"Point","coordinates":[-116.9718333,33.8213333,12.71]}]	Feature	Point	

Open in new window


I appreciate the help
g_currierAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what is the output you want?
0
g_currierAuthor Commented:
ideally, it would be [number,number,number] or, looking at the data itself:
 "geometry":{"type":"Point","coordinates[-121.5635,36.9511667,5.63]"},

Open in new window


the bold text (with brackets)...I can manipulate it from there - just so that each record/row has the geometry set that belongs to it.
0
g_currierAuthor Commented:
forgot to mention (it's probably obvious) the geometry object represents latitude, longitude and depth
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
you don't need any of the other data?

you just need 4 rows of 3 columns ?
0
g_currierAuthor Commented:
I have everything else...once the next json_table is added it is laterally joined to the rest in the from list (the other query which grabs the "properties" object.  I just isolated this object  so that the code is simpler to work on...
0
g_currierAuthor Commented:
Incidentally, this query is designed for use on Oracle 12.0.2...(with JSON support)
0
sdstuberCommented:
so you're just trying to index into the coordinates array?

If so, try this...

SELECT g.*
  FROM eq_data a, json_table(a.data,
                  '$.features[*]'
                  columns(dataset_type path '$.type',
                          nested path '$.geometry'
                          columns(geom_type path '$.type',
                                  longitude path '$.coordinates[0]',
                                  latitude path '$.coordinates[1]',
                                  depth path '$.coordinates[2]'
                                  ))) g;
0
sdstuberCommented:
and if that's correct,  then I think what you're looking for in the overall query is something like this...


select a.data.metadata.generated gen_date,
       a.data.metadata.url       url,
       a.data.metadata.title     title,
       a.data.metadata.status    status,
       a.data.metadata.api       api,
       a.data.metadata.count     eq_count,
       p.*
  from eq_data a,
       json_table(a.data,
                  '$.features[*]'
                  columns(mag number path '$.properties.mag',
                          place varchar2(100 char) path '$.properties.place',
                          time Integer path '$.properties.time',
                          updated Integer path '$.properties.updated',
                          tz Integer path '$.properties.tz',
                          url varchar2(100 char) path '$.properties.url',
                          detail varchar2(100 char) path '$.properties.detail',
                          felt Integer path '$.properties.felt',
                          cdi number path '$.properties.cdi',
                          mmi number path '$.properties.mmi',
                          alert varchar2(100 char) path '$.properties.alert',
                          status varchar2(100 char) path '$.properties.status',
                          tsunami Integer path '$.properties.tsunami',
                          sig Integer path '$.properties.sig',
                          net varchar2(100 char) path '$.properties.net',
                          code varchar2(100 char) path '$.properties.code',
                          ids varchar2(100 char) path '$.properties.ids',
                          sources varchar2(100 char) path '$.properties.sources',
                          types varchar2(100 char) path '$.properties.types',
                          nst Integer path '$.properties.nst',
                          dmin number path '$.properties.dmin',
                          rms number path '$.properties.rms',
                          gap number path '$.properties.gap',
                          magType varchar2(100 char) path '$.properties.magType',
                          type varchar2(100 char) path '$.properties.type',
                          nested path '$.geometry'
                          columns(geom_type path '$.type',
                                  longitude path '$.coordinates[0]',
                                  latitude path '$.coordinates[1]',
                                  depth path '$.coordinates[2]'))) p;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
g_currierAuthor Commented:
now that's a "schooling" I can take with me... thanks!  that's perfect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.