work with json in oracle

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 requeststatus := 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 = OKAddress = "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    }  }}

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

I wrote this library : http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en, and this works great to get some json response into a plsql table.

If you only want to extract Oracle data and transform it in Json, this library is a bit "Heavy to use"...I can propose you another code doing it better and faster :

create or replace package jsonfly asprocedure open_object(k varchar2 default null);procedure close_object;procedure open_array (k varchar2 default null);procedure close_array;procedure separation;procedure member(k varchar2, v varchar2);procedure member(k varchar2, n number);procedure send;end;/create or replace package body jsonfly as---------------------------------------------------------------------------------- package pour générer du JSON, envoyé à la volé--------------------------------------------------------------------------------type tCache is table of varchar2(2000) index by binary_integer;g_openBrace         constant varchar2(2) := '{ ';g_closeBrace        constant varchar2(2) := ' }';g_openBracket       constant varchar2(2) := '[ ';g_closeBracket      constant varchar2(2) := ' ]';g_stringDelimiter   constant varchar2(1) := '"';g_Affectation       constant varchar2(3) := ' : ';g_separation        constant varchar2(3) := ', ';g_CR                constant varchar2(1) := Chr(10); -- used to indent the JSON object correctlyg_spc               constant varchar2(2) := '  ';     -- used to indent the JSON object correctlyg_js_comment_open   constant varchar2(20) := '/*-secure-\n'; -- used to prevent from javascript hijackingg_js_comment_close  constant varchar2(20) := '\n*/';          -- used to prevent from javascript hijacking--isObjectOpened  boolean := false;--isArrayOpened   boolean := false;t tCache;i number := 1;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure p(s  varchar2) isbegin    t(i) := s;    i := i + 1;end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------function encap (s varchar2) return varchar2 isbegin    return g_stringdelimiter || s || g_stringdelimiter;end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------function encode_string(p_string varchar2) return varchar2 isbegin    return replace(replace(replace(replace(replace(replace(replace(replace(p_string,         '\', '\\'),         '"', '\"'),         '/', '\/'),         chr(8), '\b'),         chr(9), '\t'),         chr(10), '\n'),         chr(12), '\f'),         chr(13), '\r');end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure open_object(k varchar2 default null) isbegin        if ( k is null ) then         p(g_openbrace);    else         p( encap(k) || g_affectation || g_openbrace);    end if; end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure close_object isbegin    if (t(i-1) = g_separation) then        i := i - 1;    end if;     p(g_closebrace);    separation();end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure open_array (k varchar2 default null) isbegin        if ( k is null ) then     p(g_openbracket);    else         p( encap(k) || g_affectation || g_openbracket);    end if; end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure close_array isbegin    if (t(i-1) = g_separation) then        i := i - 1;    end if;     p(g_closebracket);    separation();end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure separation isbegin    p(g_separation);end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure key(k varchar2) isbegin   p( encap(k) || g_affectation);end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure value(v varchar2) isbegin   p(v);end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure member(k varchar2, v varchar2) isbegin    p( encap(k) || g_affectation || encap(encode_string(v)));    p(g_separation);end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure member(k varchar2, n number) isbegin    p( encap(k) || g_affectation || n );    p(g_separation);end;---------------------------------------------------------------------------------- --------------------------------------------------------------------------------procedure send isbegin    if (t(i-1) = g_separation) then        t.delete(i-1);    end if;     i := t.first;    while (i is not null) loop        htp.p(t(i));        i := t.next(i);    end loop;end;end jsonfly;/