How to parse a json tree using plsql pljson framework?

Question

I am using a webservice http://maps.google.com/maps/api/geocode/json?address=mysore to get location data in my plsql code. I am able to access the first level data using help from work with json in oracle. But this provides help to get first level data from json.

I need to further get the lat and lng values. Can anyone help me with this?

Location = {
  "bounds" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  },
  "location" : {
    "lat" : 44.9330076,
    "lng" : -93.16290629999999
  },
  "location_type" : "APPROXIMATE",
  "viewport" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  }
}

This is my code to get the address from the google maps api. I need to fetch latitutde, longitude and formatted_address from the response.

CREATE OR REPLACE PROCEDURE geo_lat_long_addr_proc(
      ADDRESS     VARCHAR2 DEFAULT 'EUR')
     IS
      v_debug_mode        BOOLEAN := TRUE;
      v_req               utl_http.req;
      v_resp              utl_http.resp;
      v_msg               VARCHAR2(80);
      v_entire_msg        VARCHAR2(32767) := NULL;
      v_conversion_factor NUMBER;
      v_url               VARCHAR2(256) :=
              'http://maps.google.com/maps/api/geocode/json?address='||
              ADDRESS
              ;
    BEGIN

      v_req := utl_http.begin_request(url => v_url,
         method => 'GET');
      v_resp := utl_http.get_response(r => v_req);

      IF    v_debug_mode
      THEN
         dbms_output.put_line('HTTP Status Return code: '||
                         v_resp.status_code);
      END IF;

      BEGIN
         LOOP
           utl_http.read_text(r => v_resp,data => v_msg);
           v_entire_msg := v_entire_msg||v_msg;
         END LOOP;
      EXCEPTION
         WHEN  utl_http.end_of_body
         THEN  null;
      END;

      IF    v_debug_mode
      THEN  dbms_output.put_line(v_entire_msg);
      END IF;

      utl_http.end_response(r => v_resp);

    EXCEPTION
      WHEN  others
      THEN  RETURN;
    END geo_lat_long_addr_proc;
    /

Answer 1

Here is a simple example:

declare
  l_geo_data clob := '{
  "bounds" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  },
  "location" : {
    "lat" : 44.9330076,
    "lng" : -93.16290629999999
  },
  "location_type" : "APPROXIMATE",
  "viewport" : {
    "northeast" : {
      "lat" : 44.9483849,
      "lng" : -93.1261959
    },
    "southwest" : {
      "lat" : 44.9223829,
      "lng" : -93.200307
    }
  }
}';

  l_geo_data_json json;
  l_bounds_json    json;
  l_northeast_json json;
  l_southwest_json json;

begin
  l_geo_data_json := json(l_geo_data);  

  dbms_output.put_line('-- one way to do it...');
  l_bounds_json    := json(l_geo_data_json.get('bounds'));

  l_northeast_json := json(l_bounds_json.get('northeast'));
  dbms_output.put_line('bounds.northeast.lat:'||l_northeast_json.get('lat').get_number);
  dbms_output.put_line('bounds.northeast.lng:'||l_northeast_json.get('lng').get_number);

  l_southwest_json := json(l_bounds_json.get('southwest'));
  dbms_output.put_line('bounds.southwest.lat:'||l_southwest_json.get('lat').get_number);
  dbms_output.put_line('bounds.southwest.lng:'||l_southwest_json.get('lng').get_number);

  dbms_output.new_line();  
  dbms_output.put_line('-- another way is to use path method if you dont care about any specific attribute but just the one you need...');
  dbms_output.put_line('bounds.northeast.lat:'||l_geo_data_json.path('bounds.northeast.lat').get_number);
  dbms_output.put_line('bounds.southwest.lat:'||l_geo_data_json.path('bounds.southwest.lat').get_number);

  dbms_output.new_line();  
  dbms_output.put_line('-- getting the location_type');
  dbms_output.put_line('location_type:' || l_geo_data_json.get('location_type').get_string);

end;

Answer 2

I was able to get lat, lng and location data using the below pljson code.

 l_response_obj     json;
   l_resultObject   json;
   l_list           json_list;
   l_locationObject   json;
   l_geometryObject   json;

l_response_obj := json(l_entire_msg);
      l_list := json_list(l_response_obj.get ('results'));

  -- Grab the first item in the list
  l_resultObject := json(l_list.head);


  -- Show the location data
  l_geometryObject := json(l_resultObject.get ('geometry'));
  l_locationObject := json(l_geometryObject.get ('location'));
  -- dbms_output.put_line ('Lat = ' || locationObject.get ('lat').TO_CHAR ());
  -- dbms_output.put_line ('Lng = ' || locationObject.get ('lng').TO_CHAR ());

  p_o_lat := l_locationObject.get ('lat').TO_CHAR ();
  p_o_lng := l_locationObject.get ('lng').TO_CHAR ();
  p_o_formatted_addr :=
     l_resultObject.get ('formatted_address').TO_CHAR ();