How do I make an ORDS query return a JSON_OBJECT without quotes?

Question

I am trying to build a JSON to return as the result of an HTTP GET using ORDS on Oracle Database XE 18c. Using JSON_OBJECT and similar functions, the SELECT always seems to surround the resulting JSON with quotes and escape the quotes within the object. Example:

CREATE TABLE test_cases (
    team VARCHAR2(3),
    response_time NUMBER
);

INSERT INTO test_cases (team, response_time) VALUES ('foo', 1);
INSERT INTO test_cases (team, response_time) VALUES ('foo', 2);
INSERT INTO test_cases (team, response_time) VALUES ('foo', 5);
INSERT INTO test_cases (team, response_time) VALUES ('bar', 5);
INSERT INTO test_cases (team, response_time) VALUES ('bar', 7);
INSERT INTO test_cases (team, response_time) VALUES ('bar', 9);
COMMIT;

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest',
    p_base_path      => 'rest/',
    p_items_per_page => 0
  );

  ORDS.define_template(
    p_module_name    => 'rest',
    p_pattern        => 'stats/'
  );

  ORDS.define_handler(
    p_module_name    => 'rest',
    p_pattern        => 'stats/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_query_one_row,
    p_source         => '
SELECT JSON_OBJECTAGG (
    KEY t.team VALUE AVG(t.response_time)
)
AS averages
FROM test_cases t 
GROUP BY t.team 
                        ',
    p_items_per_page => 0
  );

  COMMIT;
END;
/

Requesting the resource gives this result:

$ curl -i -H "Content-Type: application/json" -X GET "http://localhost:8080/ords/rest/stats/"
HTTP/1.1 200 OK
Content-Type: application/json
{"averages":"{\"foo\":2.66666666666666666666666666666666666667,\"bar\":7}"}

where the JSON value of "averages" is quoted. The behavior seems to be particular to JSON_OBJECT, because other SELECT calls using the same ORDS parameters do not add quotes to the results.

Is there a way to de-stringify the output of JSON_OBJECT before building it into the result of the SELECT?

Answer 1

Since you're generating the JSON yourself, you want to change your SOURCE_TYPE to Media Resource. Then in your query, the first column will be your mime type so your browser knows how to handle the incoming binary data.

Try this -

BEGIN
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'rest',
      p_pattern        => 'stats/',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT ''application/json'', JSON_OBJECTAGG (
    KEY t.team VALUE AVG(t.response_time)
)
AS averages
FROM test_cases t 
GROUP BY t.team '
      );

  COMMIT; 
END;
/

Then I make the GET call in my browser -

{
"foo": 2.6666666666666665,
"bar": 7
}

Now, let's say you also have some regular data, but only ONE of the columns is JSON being stored in or generated by the DB - you want ORDS to JSON-ify your regular data, but not the data that is ALREADY json.

There's a way to have your cake and eat it too, on a silver spoon.

BEGIN
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'rest',
      p_pattern        => 'stats/',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page =>  0,
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'SELECT JSON_OBJECTAGG (
    KEY t.team VALUE AVG(t.response_time)
)
"{}jsons"
FROM test_cases t 
GROUP BY t.team '
      );

  COMMIT; 
END;
/

For your JSON data, add this column alias

"{}jsons"

Running this now looks like so -