Wednesday, September 10, 2014

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:

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 ;-)


No comments:

Post a Comment