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