work with json in oracle

Solution 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
    }
  }
}

Solution 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