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

Question

Background

I need to fetch a few thousands rows from Oracle and convert them to JSON for use in SlickGrid. Currently I am fetching the rows in PHP, converting it from ISO to UTF-8 with iconv and exporting to json with json_encode. The whole operation takes about 1 second on DB side and 5 seconds to generate JSON. It is way to long.

The question

I have read that Oracle 12c supports JSON, but I cannot find exactly what I need.

Is there a way to return the result of a standard sql query in a json format?

supposedly I would like to issue a query similar to this:

SELECT * from table AS JSON 

and receive a valid json similar to this:

[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}] 

An important thing is that I need to have the unicode sequences escaped for me, as I use ISO-8859-2 charset on the client side, and JSON have to be in either UTF-8 or have the sequences escaped.

Answer 1

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; / 

Answer 2

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