Return results of a sql query as JSON in oracle 12c

Oracle 12c version 12.1.0.2 (the latest version as of 11.11.2014) adds JSON support:https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC

It's been available since October 17th. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1

If you are unable to patch/work with that version there is an excellent package written by Lewis Cunningham and Jonas Krogsboell: PL/JSON * http://pljson.sourceforge.net/

It's an excellent package (I have used it in numerous database installations).

The examples included are good and cover most scenarios.

declare   ret json;begin  ret := json_dyn.executeObject('select * from tab');  ret.print;end;/

12cR2 (available in the Oracle Cloud) supports this natively.

SQL> select JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME,LAST_NAME) from HR.EMPLOYEES;JSON_ARRAY(EMPLOYEE_ID,FIRST_NAME,LAST_NAME)--------------------------------------------------------------------------------[100,"Steven","King"][101,"Neena","Kochhar"]

or

SQL> select JSON_OBJECT('ID' is EMPLOYEE_ID , 'FirstName' is FIRST_NAME,'LastName' is LAST_NAME) from HR.EMPLOYEES;JSON_OBJECT('ID'ISEMPLOYEE_ID,'FIRSTNAME'ISFIRST_NAME,'LASTNAME'ISLAST_NAME)----------------------------------------------------------------------------{"ID":100,"FirstName":"Steven","LastName":"King"}{"ID":101,"FirstName":"Neena","LastName":"Kochhar"}

The release 12.2 includes new capabilities for generating JSON documents directly from SQL queries. The easiest way to achieve the goal is to use the functions: JSON_OBJECT and JSON_ARRAYAGG.

create table tab as    select level col1, 'value '||level col2 from dual connect by level <= 2/ select max (rownum) rn, json_arrayagg (    json_object (        key 'col1' value col1,        key 'col2' value col2    ) format json returning clob ) as json_docfrom tab;

Result:

        RN JSON_DOC                                                                        ---------- ---------------------------------------------------------         2 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}] 

Test with large amount of data:

select rn, length (json_doc) json_size, json_doc from (    <query mentoined above here>    cross join (select dummy from dual connect by level <= 1e5)     );        RN  JSON_SIZE JSON_DOC                                                                        ---------- ---------- ---------------------------------------------------------    200000    5600001 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"},

On the slow test machine it took ~1 sec. to create 5,6M JSON.

In the release 19c the syntax of the the function JSON_OBJECT is simplified.
The query above will look now like this:

select json_arrayagg (      json_object (*) returning clob   ) as json_doc  from tab;

On Live SQL.