work with json in oracle

Question

Is there an easy way to work with JSON within oracle? I have a standard procedure that I use to call web services quite often, JSON is a format that I am familiar with in web development context, but what is the best way to work with json within a stored procedure? For instance take the CLOB response from the URI, convert that to a JSON object and get a value from that?

For reference sake, here is the procedure I used to fetch URLs

create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is    req     Utl_Http.req;    resp    Utl_Http.resp;    NAME    VARCHAR2 (255);    VALUE   VARCHAR2 (1023);    v_msg   VARCHAR2 (80);    v_ans clob; --   v_url   VARCHAR2 (32767) := 'http://www.macalester.edu/'; BEGIN    /* request that exceptions are raised for error Status Codes */    Utl_Http.set_response_error_check (ENABLE => TRUE );    /* allow testing for exceptions like Utl_Http.Http_Server_Error */    Utl_Http.set_detailed_excp_support (ENABLE => TRUE );    /*    Utl_Http.set_proxy (       proxy                 => 'www-proxy.us.oracle.com',       no_proxy_domains      => 'us.oracle.com'    );    */    req := Utl_Http.begin_request (url => v_url, method => 'GET');    /*     Alternatively use method => 'POST' and Utl_Http.Write_Text to     build an arbitrarily long message   */    /*    Utl_Http.set_authentication (       r              => req,       username       => 'SomeUser',       PASSWORD       => 'SomePassword',       scheme         => 'Basic',       for_proxy      => FALSE      --this info is for the target Web server     );    */     Utl_Http.set_header (r => req, NAME => 'User-Agent', VALUE => 'Mozilla/4.0');    resp := Utl_Http.get_response (r => req);    /*    DBMS_OUTPUT.put_line ('Status code: ' || resp.status_code);    DBMS_OUTPUT.put_line ('Reason phrase: ' || resp.reason_phrase);    FOR i IN 1 .. Utl_Http.get_header_count (r => resp)    LOOP       Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);       DBMS_OUTPUT.put_line (NAME || ': ' || VALUE);    END LOOP;    */ --test    BEGIN       LOOP          Utl_Http.read_text (r => resp, DATA => v_msg);          --DBMS_OUTPUT.put_line (v_msg);          v_ans := v_ans || v_msg;          url_resp := url_resp || v_msg;       END LOOP;    EXCEPTION       WHEN Utl_Http.end_of_body       THEN          NULL;    END; --test    Utl_Http.end_response (r => resp);      --url_resp := v_ans;  EXCEPTION    /*     The exception handling illustrates the use of "pragma-ed" exceptions     like Utl_Http.Http_Client_Error. In a realistic example, the program     would use these when it coded explicit recovery actions.      Request_Failed is raised for all exceptions after calling     Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )     And it is NEVER raised after calling with ENABLE=>TRUE   */    WHEN Utl_Http.request_failed    THEN       DBMS_OUTPUT.put_line (          'Request_Failed: ' || Utl_Http.get_detailed_sqlerrm       );       url_resp :='Request_Failed: ' || Utl_Http.get_detailed_sqlerrm;    /* raised by URL http://xxx.oracle.com/ */    WHEN Utl_Http.http_server_error    THEN       DBMS_OUTPUT.put_line (          'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm       );       url_resp := 'Http_Server_Error: ' || Utl_Http.get_detailed_sqlerrm;    /* raised by URL http://otn.oracle.com/xxx */    WHEN Utl_Http.http_client_error    THEN       DBMS_OUTPUT.put_line (          'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm       );       url_resp := 'Http_Client_Error: ' || Utl_Http.get_detailed_sqlerrm;    /* code for all the other defined exceptions you can recover from */    WHEN OTHERS    THEN       DBMS_OUTPUT.put_line (SQLERRM);       url_resp := SQLERRM; END; 

Then to test it

begin   macp_url_get(url_resp => :url_resp,                'http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false'); end; 

(I know that the googleapi will allow xml response, but there are other web APIs that I use regularly that default to JSON)

Answer 1

I have started using this library, and it seems promising: https://github.com/pljson/pljson

Easy to install, and the examples are good.

To use the library in your example, add these variables to your procedure..

mapData     json; results     json_list; status      json_value; firstResult json; geometry    json; 

....

Then you can manipulate the response as a json object.

-- convert the result from the get to a json object, and show some results. mapData := json(v_ans);  -- Show the status of the request status := mapData.get('status'); dbms_output.put_line('Status = ' || status.get_string());  IF (status.get_string() = 'OK') THEN   results := json_list(mapData.get('results'));   -- Grab the first item in the list   resultObject := json(results.head);    -- Show the human readable address    dbms_output.put_line('Address = ' || resultObject.get('formatted_address').to_char() );   -- Show the json location data    dbms_output.put_line('Location = ' || resultObject.get('geometry').to_char() ); END IF; 

Running this code will output this to the dbms output:

Status = OK Address = "St Paul, MN 55105, USA" 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     }   } } 

Answer 2

It should be noted that as of Oracle 12c there is some native support of JSON. However i don't think in the current form it's as useful as the like of PLJSON included in another answer.

To use the feature you create a table with a BLOB, CLOB or Varchar2 field and add a constraint against it "column IS JSON". This enforces JSON syntax checking on that column.

As long as the "IS JSON" constraint is in place you can access the JSON values within using dot notation from SQL. To me, it doesn't seem to provide as powerful manipulation as PLJSON. You can also create an XMLType and then convert to JSON.

Useful links:
Oracle docs
Good tutorial and examples
Tutorial including XML to JSON