Return results of a sql query as JSON in oracle 12

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

I do not see Python solution (in case you need to dump JSON).

I wrote json-ora-extract for medium size extracts (because data-set has to fit available memory).

It uses wx_Oracle and json Python modules to read data from Oracle data base (any version) and dump it into *.json file.

There's also an option to create compressed *.gz file.

Answer 2

To add to the answer in oracle 12.2 , you can create json as you want like this .

SELECT JSON_ARRAY(
JSON_OBJECT (
         KEY 'number' VALUE s.number,
         KEY 'name' VALUE s.sname,
         KEY 'location' VALUE s.loc
          )
       ) AS student_det
FROM   student s;

Answer 3

Oracle 12c support for JSON is an ability to store JSON objects, query them and select from them.

You have tabular format and only need to display your data as a JSON. So you can simply concatenate rows into {'col1': 'rowN1', 'col2': 'rowN2'} and make the rest on a client side. Or you can use LISTAGG to get the whole document. Example: http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/

Just mind the SQL VARCHAR2 limit of 4000 characters.

You could also look into http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/ But I don't think, that oracle object type will improve your performance.

Another aproach is to export XML using XMLType. Then convert XML to JSON. XMLType will take care of special characters, and API is quite stable (you will not need to rewrite your program for Oracle 14).

Answer 4

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

Answer 5

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 6

You can use the xmltype to convert the result of an SQL into XML and JSON. See the following article for the solution which will work for Oracle since version 9. You can also download the package itstar_xml_util:

http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies

A simple example with the emp table:

declare
  l_sql_string varchar2(2000);
  l_xml        xmltype;
  l_json       xmltype;
begin
  l_sql_string := 'select a.empno, a.ename, a.job from emp a';

  -- Create the XML aus SQL
  l_xml := itstar_xml_util.sql2xml(l_sql_string);

  -- Display the XML
  dbms_output.put_line(l_xml.getclobval());

  l_json := itstar_xml_util.xml2json(l_xml);
  -- Display the JSON
  dbms_output.put_line(l_json.getclobval());  
end;

The result looks like this:

{"ROWSET": [
    {
      "EMPNO": 7839,
      "ENAME": "KING",
      "JOB": "PRESIDENT"
    },
    {
      "EMPNO": 7698,
      "ENAME": "BLAKE",
      "JOB": "MANAGER"
    },
[...]
    {
      "EMPNO": 7934,
      "ENAME": "MILLER",
      "JOB": "CLERK"
    }
  ]}