CODEWITHSUNDEEP
jsonoracle-databaseoracle12c
SWilk
SWilk

Reputation: 3464

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

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.

Upvotes: 33

Views: 165554

Answers (10)

0xdb
0xdb

Reputation: 3697

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_doc
from tab;

Result:

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

Test with a relatively 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 db<>fiddle.

Upvotes: 16

henrry
henrry

Reputation: 623

I test in 19C:

SQL> select JSON_OBJECT(*) from HR.EMPLOYEES;
------------------------------------------------------------------------------
{"ID":100,"FirstName":"Steven","LastName":"King", ...}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar", ...}

Or:

 SQL> select json_arrayagg(JSON_OBJECT(*)  returning clob  ) from HR.EMPLOYEES;
------------------------------------------------------------------------------
[ {"ID":100,"FirstName":"Steven","LastName":"King", ...},{"ID":101,"FirstName":"Neena","LastName":"Kochhar", ...}]

Upvotes: 3

PT_STAR
PT_STAR

Reputation: 505

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

Upvotes: 7

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

Starting Oracle 19c, the syntax to construct a JSON representation for a row of a table is simplified

For Eg: To convert all the rows of the hr.employees to separate jsons, use

SELECT JSON_OBJECT(*) FROM hr.employees ;

{
"EMPLOYEE_ID" : 100,
"FIRST_NAME" : "Steven",
"LAST_NAME" : "King",
"EMAIL" : "SKING",
"PHONE_NUMBER" : "515.123.4567",
"HIRE_DATE" : "2003-06-17T00:00:00",
"JOB_ID" : "AD_PRES",
"SALARY" : 24000,
"COMMISSION_PCT" : null,
"MANAGER_ID" : null,
"DEPARTMENT_ID" : 90
}                       --row 1
{
"EMPLOYEE_ID" : 101,
"FIRST_NAME" : "Neena",
"LAST_NAME" : "Kochhar",
"EMAIL" : "NKOCHHAR",
"PHONE_NUMBER" : "515.123.4568",
"HIRE_DATE" : "2005-09-21T00:00:00",
"JOB_ID" : "AD_VP",
"SALARY" : 17000,
"COMMISSION_PCT" : null,
"MANAGER_ID" : 100,
"DEPARTMENT_ID" : 90
}                       --row 2
 ...

LIVE SQL example

Upvotes: 9

Shahbaz Ali
Shahbaz Ali

Reputation: 81

Just try this:

:) life is happy

with data as
  ( select 
    xmlelement(e,regexp_replace('{"name":"'||colname||'"}', '[[:cntrl:]]', ''),',') col1
    from tblname
  )
  select
        rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'||'quot;','"'),'<E>',''),'</E>',''),',')
        as very_long_json
  from data;

Upvotes: 3

Himanshu sharma
Himanshu sharma

Reputation: 7901

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;

Upvotes: 2

olekb
olekb

Reputation: 648

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.

Upvotes: -2

mark d drake
mark d drake

Reputation: 1505

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

Upvotes: 28

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

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

Upvotes: 20

vav
vav

Reputation: 4694

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).

Upvotes: 2

Related Questions