Mostly Making oracle easy, mostly:
Search for:
ThatJeffSmith
In ORDS

JSON_OBJECT_T and ORDS: what’s the ‘best way?’

2 Mins Read

Much of the content you find here is coming directly from people like you – googling for things like ‘JSON_OBJECT_T and ORDS.’

Hi Jeff, what would be the best way when having a function returning JSON_OBJECT_T?

PaulJ

‘Best’ is such a fun word. I would say Paul, that it depends. But let’s look at maybe the most straightforward way, just printing it.

JSON_OBJECT_T is a PL/SQL structure that allows us to work with JSON in PL/SQL (Oracle Docs.)

So let’s assume we’re going to have a GET handler, and we’re doing to have a PL/SQL block of code do the work, presumably generate some JSON using this PL/SQL object type of JSON_OBJECT_T.

The code

DECLARE
  jo JSON_OBJECT_T;
  jc_raw clob;
  jc_pretty clob;
  keys_string VARCHAR2(100);
 
BEGIN
  jo := JSON_OBJECT_T.parse('{"name":"fred",
                              "jobTitle":"codemonkey",
                              "projects":["json", "xml"]}');
 
  jc_raw := jo.TO_CLOB;
 
  SELECT JSON_SERIALIZE(jc_raw returning clob PRETTY) INTO jc_pretty FROM dual;
  owa_util.mime_header( 'application/json', TRUE, 'UTF-8' );
  htp.p(jc_pretty);
 
END;

Let’s call it now

Build up your object, convert it to a CLOB, print it.

Is this ‘the best?’

It’s perhaps the most straightforward. Put your JSON into a clob or blob, then print it with HTP.P. I’m being nice and using OWA to set the mime type in the response header…so the call to JSON_SERIALIZE with the PRETTY option isn’t necessary if your browser has a JSON extension to pretty format the response for you.

I’m just a fan of the JSON functions in general. Beda and his crew have built a really nice solution for both Oracle developers that dig relational but also want the flexibility of JSON AND for those developers that want to do everything in JSON but have the power, security, and features of the Oracle Database.

Hint: Read Beda’s blog to see what’s possible with JSON in the Oracle Database.

And of course he’s on Twitter as well.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

4 Comments

  1. PaulJ Reply

    Hi Jeff, thanks for your post!

    Some findings:
    – apex_util.prn is a good option to print CLOBs (htp.p will start complaining once the serialized JSON is >VARCHAR2)

    – printing a serialized JSON_OBJECT_T is significantly slower than with APEX_JSON (?!)

    If you have for example the exact same object as JSON_OBJECT_T and as APEX_JSON, you’ll see that “apex_util.prn(v_json_obj.to_clob, false);” is much slower than “apex_util.prn(apex_json.get_clob_output, false);”.

    The reason seems to be, that the generated CLOBs are different; APEX_JSON escapes string values (“O’Brien” becomes “O\u0027Brien”) resulting in a bigger CLOB, but faster print performance.

    Escaping string values in JSON_OBJECT_T (using e.g. apex_escape.json) improves the print performance, but the serialization messes up the output by adding additional backslashes (“O’Brien” becomes “O\\u0027Brien”):

    declare
    v_json json_object_t := json_object_t();
    begin
    dbms_output.put_line(apex_escape.json(‘O”Brien’));
    — O\u0027Brien

    v_json.put (‘name1’, apex_escape.json(‘O”Brien’));
    v_json.put (‘name2’, ‘O\u0027Brien’);

    dbms_output.put_line(v_json.to_clob);
    — {“name1″:”O\\u0027Brien”,”name2″:”O\\u0027Brien”}
    end;

    BR Paul

    • thatjeffsmith Post Author

      The APEX helper routines are GREAT, IF YOU HAVE APEX installed. I’ve been advocating we promote those to be core database plsql APIs, so no apex required.

  2. Ulrik Reply

    Thanks Jeff, just wanted to mention that the code has two DECLARE…

Write A Comment