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

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

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.

“Get Started with the New Oracle Database API for MongoDB”
see how you can run your MongoDB applications, drivers, tools against an Oracle Database with SQL support over MongoDB collections!https://t.co/vwh9OEjC0i#Oracle #Autonomous #MongoDB #SQL #database #NoSQL pic.twitter.com/0STwKLCvyY

— Beda Hammerschmidt (@bch_t) May 20, 2022