JSON_TABLE or XMLTABLE - comparison with geocoding
Previously I've demonstrated how to use function XMLTABLE to query Google maps routing directions. Now Oracle version 12.1.0.2.0 has function JSON_TABLE to do similar querying on JSON data rather than XML data. So let's try that out and spot some differences...
For this test we'll use the Google maps geocoding rather than routing directions and try to geocode the address of Oracle headquarters. If we call the Google api with instructions to return JSON data to us, we can do this select:
Column ADR will be a CLOB with this content:
We can pick out the desired data from the JSON output using JSON_TABLE:
Which give this output:
Each address component becomes a row in the output due to the NESTED PATH. So if we want just specific address components in a single row, we can pivot the output:
Which gives us this output:
Let's compare to using XML instead:
Column ADR here will be an XMLTYPE with this content:
So using XMLTABLE we can pick out the desired data from the XMLTYPE:
Which will give us this identical output:
So we spot some differences between JSON_TABLE syntax and XMLTABLE syntax:
For this test we'll use the Google maps geocoding rather than routing directions and try to geocode the address of Oracle headquarters. If we call the Google api with instructions to return JSON data to us, we can do this select:
select httpuritype(
'http://maps.googleapis.com/maps/api/geocode/json?address='||
utl_url.escape('300 oracle pkwy, redwood, california')
).getclob() adr
from dual
/
Column ADR will be a CLOB with this content:
{
"results" : [
{
"address_components" : [
{
"long_name" : "300",
"short_name" : "300",
"types" : [ "street_number" ]
},
{
"long_name" : "Oracle Parkway",
"short_name" : "Oracle Pkwy",
"types" : [ "route" ]
},
{
"long_name" : "Redwood Shores",
"short_name" : "Redwood Shores",
"types" : [ "neighborhood", "political" ]
},
{
"long_name" : "Redwood City",
"short_name" : "Redwood City",
"types" : [ "locality", "political" ]
},
{
"long_name" : "California",
"short_name" : "CA",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
},
{
"long_name" : "94065",
"short_name" : "94065",
"types" : [ "postal_code" ]
}
],
"formatted_address" : "300 Oracle Parkway, Redwood City, CA 94065, USA",
"geometry" : {
"location" : {
"lat" : 37.5311942,
"lng" : -122.2646403
},
"location_type" : "ROOFTOP",
"viewport" : {
"northeast" : {
"lat" : 37.5325431802915,
"lng" : -122.2632913197085
},
"southwest" : {
"lat" : 37.52984521970851,
"lng" : -122.2659892802915
}
}
},
"partial_match" : true,
"types" : [ "street_address" ]
}
],
"status" : "OK"
}
We can pick out the desired data from the JSON output using JSON_TABLE:
select geo.*
from json_table(
httpuritype(
'http://maps.googleapis.com/maps/api/geocode/json?address='||
utl_url.escape('300 oracle parkway, redwood, california')
).getclob()
, '$' columns (
status varchar2(20) path '$.status'
, nested path '$.results[*]' columns (
lat number path '$.geometry.location.lat'
, lng number path '$.geometry.location.lng'
, formatted_address varchar2(100) path '$.formatted_address'
, nested path '$.address_components[*]' columns (
long_name varchar2(100) path '$.long_name'
, nested path '$.types[*]' columns (
component_type varchar2(100) path '$'
)
)
)
)
) geo
/
Which give this output:
STATUS LAT LNG FORMATTED_ADDRESS LONG_NAME COMPONENT_TYPE ------ ---------- ---------- ----------------------------------------------- ---------------- --------------------------- OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA 300 street_number OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA Oracle Parkway route OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA Redwood Shores neighborhood OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA Redwood Shores political OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA Redwood City locality OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA Redwood City political OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA San Mateo County administrative_area_level_2 OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA San Mateo County political OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA California administrative_area_level_1 OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA California political OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA United States country OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA United States political OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA 94065 postal_code 13 rows selected.
Each address component becomes a row in the output due to the NESTED PATH. So if we want just specific address components in a single row, we can pivot the output:
select geo.*
from json_table(
httpuritype(
'http://maps.googleapis.com/maps/api/geocode/json?address='||
utl_url.escape('300 oracle parkway, redwood, california')
).getclob()
, '$' columns (
status varchar2(20) path '$.status'
, nested path '$.results[*]' columns (
lat number path '$.geometry.location.lat'
, lng number path '$.geometry.location.lng'
, formatted_address varchar2(100) path '$.formatted_address'
, nested path '$.address_components[*]' columns (
long_name varchar2(100) path '$.long_name'
, nested path '$.types[*]' columns (
component_type varchar2(100) path '$'
)
)
)
)
) pivot (
max(long_name) name
for component_type in (
'street_number' as street_number
, 'route' as street
, 'locality' as city
, 'administrative_area_level_1' as state
, 'postal_code' as zip
, 'country' as country
)
) geo
/
Which gives us this output:
STATUS LAT LNG FORMATTED_ADDRESS STR STREET_NAME CITY_NAME STATE_NAME ZIP_N COUNTRY_NAME ------ ---------- ---------- ----------------------------------------------- --- -------------- ------------ ---------- ----- ------------- OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA 300 Oracle Parkway Redwood City California 94065 United States
Let's compare to using XML instead:
select httpuritype(
'http://maps.googleapis.com/maps/api/geocode/xml?address='||
utl_url.escape('300 oracle pkwy, redwood, california')
).getxml() adr
from dual
/
Column ADR here will be an XMLTYPE with this content:
<?xml version="1.0" encoding="UTF-8"?>
<GeocodeResponse>
<status>OK</status>
<result>
<type>street_address</type>
<formatted_address>300 Oracle Parkway, Redwood City, CA 94065, USA</formatted_address>
<address_component>
<long_name>300</long_name>
<short_name>300</short_name>
<type>street_number</type>
</address_component>
<address_component>
<long_name>Oracle Parkway</long_name>
<short_name>Oracle Pkwy</short_name>
<type>route</type>
</address_component>
<address_component>
<long_name>Redwood Shores</long_name>
<short_name>Redwood Shores</short_name>
<type>neighborhood</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Redwood City</long_name>
<short_name>Redwood City</short_name>
<type>locality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>California</long_name>
<short_name>CA</short_name>
<type>administrative_area_level_1</type>
<type>political</type>
</address_component>
<address_component>
<long_name>United States</long_name>
<short_name>US</short_name>
<type>country</type>
<type>political</type>
</address_component>
<address_component>
<long_name>94065</long_name>
<short_name>94065</short_name>
<type>postal_code</type>
</address_component>
<geometry>
<location>
<lat>37.5311942</lat>
<lng>-122.2646403</lng>
</location>
<location_type>ROOFTOP</location_type>
<viewport>
<southwest>
<lat>37.5298452</lat>
<lng>-122.2659893</lng>
</southwest>
<northeast>
<lat>37.5325432</lat>
<lng>-122.2632913</lng>
</northeast>
</viewport>
</geometry>
<partial_match>true</partial_match>
</result>
</GeocodeResponse>
So using XMLTABLE we can pick out the desired data from the XMLTYPE:
select geo.*
from xmltable(
'/GeocodeResponse'
passing httpuritype(
'http://maps.googleapis.com/maps/api/geocode/xml?address='||
utl_url.escape('300 oracle parkway, redwood, california')
).getxml()
columns
status varchar2(20) path 'status'
, lat number path 'result/geometry/location/lat'
, lng number path 'result/geometry/location/lng'
, formatted_address varchar2(100) path 'result/formatted_address'
, street_number_name varchar2(100) path 'result/address_component[type="street_number"]/long_name'
, street_name varchar2(100) path 'result/address_component[type="route"]/long_name'
, city_name varchar2(100) path 'result/address_component[type="locality"]/long_name'
, state_name varchar2(100) path 'result/address_component[type="administrative_area_level_1"]/long_name'
, zip_name varchar2(100) path 'result/address_component[type="postal_code"]/long_name'
, country_name varchar2(100) path 'result/address_component[type="country"]/long_name'
) geo
/
Which will give us this identical output:
STATUS LAT LNG FORMATTED_ADDRESS STR STREET_NAME CITY_NAME STATE_NAME ZIP_N COUNTRY_NAME ------ ---------- ---------- ----------------------------------------------- --- -------------- ------------ ---------- ----- ------------- OK 37.5311942 -122.26464 300 Oracle Parkway, Redwood City, CA 94065, USA 300 Oracle Parkway Redwood City California 94065 United States
So we spot some differences between JSON_TABLE syntax and XMLTABLE syntax:
- JSON_TABLE has underscore in the name
XMLTABLE does not ;-) - JSON_TABLE operates on CLOB data
XMLTABLE operates on the specific XMLTYPE datatype - JSON_TABLE first argument is the JSON data, followed by the path you wish to query, and then columns with parentheses
XMLTABLE first argument is the path (XQuery) you wish to query, followed by PASSING the XML data, and then columns without parentheses - JSON_TABLE use simple JSON dot-notation path expressions with $ for root node
XMLTABLE use fullfledged XQUERY syntax with / for root node and seperator - JSON_TABLE supports NESTED TABLE for querying JSON arrays in the data
XMLTABLE does not, you will need multiple successive XMLTABLE calls (example here) - JSON_TABLE in the simple JSON notation cannot query paths dependent on values
XMLTABLE with XPATH expressions can query like address_component[type="country"]/...
XML has had many use cases for years and will still be relevant many times. In the world of webservices XML particularly is needed for SOAP and sometimes for REST as well. The Oracle database has supported XML in many ways for years and years, both for creating XML, querying XML, storing XML and indexing XML.
Now in Oracle version 12.1.0.2.0 support for JSON has begun. Not yet as full fledged as the XML support, for example there is not yet functions for creating JSON data. But JSON is often also used for simpler cases and seems to have become very popular for REST webservices, and being able now to query JSON as easily as XML is a very nice feature indeed.
Small syntax differences may confuse a bit in the beginning if you are used to XMLTABLE syntax, but that is probably just a matter of getting used to it ;-)
Comments
Post a Comment