blocks|key|154781|text|Oracle+12c对JSON的支持是一种存储、查询和选择JSON对象的能力。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|154782|您有表格格式，只需要将数据显示为JSON。因此，您可以简单地将行连接到{'col1'：'rowN1'，'col2'：'rowN2'}中，然后在客户端进行其余操作。或者，您可以使用LISTAGG获取整个文档。示例：http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/|offset|length|154783|只要注意SQL+VARCHAR2不能超过4000个字符的限制。|154784|您也可以研究http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/，但我不认为oracle对象类型会提高您的性能。|154785|另一种方法是使用XMLType导出XML。然后将XML转换为JSON。XMLType将处理特殊字符，并且非常稳定(您不需要在Oracle14中重写程序)。|154786|entityMap|0|LINK|mutability|MUTABLE|url|http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/|1|http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/^0|0|2Y|36|0|0|0|6|25|1|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@$D|X|E|Y|1|Z]]|A|$]]|$1|F|3|G|5|6|7|10|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]|T|$5|O|P|Q|A|$R|U]]]]

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: 
<a href="http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/" rel="nofollow">http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/</a>

Just mind the SQL VARCHAR2 limit of 4000 characters.

You could also look into <a href="http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/" rel="nofollow">http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/</a> 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).

blocks|key|158468|text|您可以使用xmltype将SQL的结果转换为XML和JSON。有关适用于Oracle版本9及以后版本的解决方案，请参阅以下文章。您还可以下载itstar_xml_util包：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|158469|http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies|offset|length|158470|emp表的一个简单示例：|158471|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;|code-block|syntax|javascript|158472|结果如下所示：|158473|{"ROWSET":+[
++++{
++++++"EMPNO":+7839,
++++++"ENAME":+"KING",
++++++"JOB":+"PRESIDENT"
++++},
++++{
++++++"EMPNO":+7698,
++++++"ENAME":+"BLAKE",
++++++"JOB":+"MANAGER"
++++},
[...]
++++{
++++++"EMPNO":+7934,
++++++"ENAME":+"MILLER",
++++++"JOB":+"CLERK"
++++}
++]}|158474|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2A|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@$D|Z|E|10|1|11]]|A|$]]|$1|F|3|G|5|6|7|12|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|13|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|14|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|15|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|16|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|C]]]]

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: 

<a href="http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies" rel="nofollow noreferrer">http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies</a>

A simple example with the emp table:

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

The result looks like this:

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

blocks|key|2956464|text|Oracle12c版本12.1.0.2+(截至2014年11月11日的最新版本)添加了JSON支持：https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2956465|它从10月17日起就可以使用了。https://blogs.oracle.com/db/entry/oracle_database_12c_release_1|2956466|如果您无法修补/使用该版本，有一个由Lewis+Cunningham和Jonas+Krogsboell编写的优秀包:+PL/JSON+*+http://pljson.sourceforge.net/|2956467|这是一个很好的包(我已经在许多数据库安装中使用过它)。|2956468|包含的示例很好，涵盖了大多数场景。|2956469|declare+
++ret+json;
begin
++ret+:=+json_dyn.executeObject('select+*+from+tab');
++ret.print;
end;
/|code-block|syntax|javascript|2956470|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC|1|https://blogs.oracle.com/db/entry/oracle_database_12c_release_1|2|http://pljson.sourceforge.net/^0|1E|1W|0|0|G|1R|1|0|1X|U|2|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@$A|17|B|18|1|19]]|C|$]]|$1|F|3|G|5|6|7|1A|8|@]|9|@$A|1B|B|1C|1|1D]]|C|$]]|$1|H|3|I|5|6|7|1E|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|C|$]]|$1|L|3|M|5|N|7|1G|8|@]|9|@]|C|$O|P]]|$1|Q|3|-4|5|6|7|1H|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]|Y|$5|T|U|V|C|$W|Z]]|10|$5|T|U|V|C|$W|11]]]]

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

It's been available since October 17th. <a href="https://blogs.oracle.com/db/entry/oracle_database_12c_release_1">https://blogs.oracle.com/db/entry/oracle_database_12c_release_1</a>

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

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

The examples included are good and cover most scenarios. 

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

blocks|key|2743897|text|12cR2+(在Oracle云中提供)本机支持此功能。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2743898|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"]|code-block|syntax|javascript|2743899|或|2743900|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"}|2743901|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>SQL&gt; 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"]
</code></pre>

or 

<pre><code>SQL&gt; 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"}
</code></pre>

blocks|key|2743918|text|我没有看到Python解决方案(以防您需要转储JSON)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2743919|我为中等大小的提取编写了json-ora-extract+(因为数据集必须适合可用内存)。|offset|length|2743920|它使用wx_Oracle和Oracle+Python模块从json数据库(任何版本)中读取数据，并将其转储到*.json文件中。|style|CODE|2743921|还有一个选项可以创建压缩的*.gz文件。|2743922|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/alexbuz/json-ora-extract^0|0|C|G|0|0|3|9|T|4|1I|6|0|D|4|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$D|Z|E|10|H|I]|$D|11|E|12|H|I]|$D|13|E|14|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|15|8|@$D|16|E|17|H|I]]|9|@]|A|$]]|$1|L|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|M|$N|$5|O|P|Q|A|$R|S]]]]

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

I wrote <a href="https://github.com/alexbuz/json-ora-extract" rel="nofollow noreferrer">json-ora-extract</a> for medium size extracts (because data-set has to fit available memory).

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

There's also an option to create compressed <code>*.gz</code> file.

blocks|key|2743935|text|为了补充oracle+12.2中的答案，您可以像这样创建json。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2743936|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;|code-block|syntax|javascript|2743937|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

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

blocks|key|154883|text|试着这样做：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|154884|:)生活是幸福的|154885|with+data+as
++(+select+
++++xmlelement(e,regexp_replace('{"name":"'%7C%7Ccolname%7C%7C'"}',+'[[:cntrl:]]',+''),',')+col1
++++from+tblname
++)
++select
++++++++rtrim(replace(replace(replace(xmlagg(col1).getclobval(),'&'%7C%7C'quot;','"'),'<E>',''),'</E>',''),',')
++++++++as+very_long_json
++from+data;|code-block|syntax|javascript|154886|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Just try this:

:) life is happy

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

blocks|key|2956541|text|从Oracle19c开始，介绍了为表的一行构造JSON表示的语法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2956542|例如:要将hr.employees的所有行转换为单独的json，请使用|offset|length|style|CODE|2956543|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
+...|code-block|syntax|javascript|2956544|2956545|entityMap^0|0|5|C|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|T|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|O|$]]

Starting Oracle 19c, the syntax to construct a JSON representation for a row of a table <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/newft/new-features.html#GUID-C0945AB8-C9E0-48A4-8D78-33631BC3F725" rel="nofollow noreferrer">is simplified</a>

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

<pre><code>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
 ...
</code></pre>

<a href="https://livesql.oracle.com/apex/livesql/s/h6l00nsmdtdsyxf1dghxa7goz" rel="nofollow noreferrer">LIVE SQL example</a>

blocks|key|157223|text|版本12.2包括直接从SQL查询生成JSON文档的新功能。实现此目标的最简单方法是使用以下函数：JSON_OBJECT和JSON_ARRAYAGG。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|157224|create+table+tab+as
++++select+level+col1,+'value+'%7C%7Clevel+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;|code-block|syntax|javascript|157225|结果：|157226|++++++++RN+JSON_DOC++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----------+---------------------------------------------------------
+++++++++2+[{"col1":1,"col2":"value+1"},{"col1":2,"col2":"value+2"}]+|157227|使用大量数据进行测试：|157228|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"},|157229|在速度较慢的测试机上，它需要大约1秒。创建5,6M+JSON。|157230|157231|在版本19c中，函数JSON_OBJECT+is+simplified的语法。|157232|上面的查询现在看起来像这样：|157233|select+json_arrayagg+(++
++++json_object+(*)+returning+clob+++
)+as+json_doc++
from+tab;|157234|在Live+SQL上。|157235|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370|1|https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_ARRAYAGG.html#GUID-6D56077D-78DE-4CC0-9498-225DDC42E054|2|https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA|3|https://livesql.oracle.com/apex/livesql/s/irib428if19k68g0v78idhlq0^0|1C|B|1O|D|1C|B|0|1O|D|1|0|0|0|0|0|0|0|0|A|B|A|P|2|0|0|0|1|8|3|0^^$0|@$1|2|3|4|5|6|7|1H|8|@$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]]|D|@$9|1M|A|1N|1|1O]|$9|1P|A|1Q|1|1R]]|E|$]]|$1|F|3|G|5|H|7|1S|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1T|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|1U|8|@]|D|@]|E|$I|J]]|$1|O|3|P|5|6|7|1V|8|@]|D|@]|E|$]]|$1|Q|3|R|5|H|7|1W|8|@]|D|@]|E|$I|J]]|$1|S|3|T|5|6|7|1X|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|1Y|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|1Z|8|@$9|20|A|21|B|C]]|D|@$9|22|A|23|1|24]]|E|$]]|$1|X|3|Y|5|6|7|25|8|@]|D|@]|E|$]]|$1|Z|3|10|5|H|7|26|8|@]|D|@]|E|$I|J]]|$1|11|3|12|5|6|7|27|8|@]|D|@$9|28|A|29|1|2A]]|E|$]]|$1|13|3|-4|5|6|7|2B|8|@]|D|@]|E|$]]]|14|$15|$5|16|17|18|E|$19|1A]]|1B|$5|16|17|18|E|$19|1C]]|1D|$5|16|17|18|E|$19|1E]]|1F|$5|16|17|18|E|$19|1G]]]]

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: <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370" rel="noreferrer"><code>JSON_OBJECT</code></a> and <a href="https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/JSON_ARRAYAGG.html#GUID-6D56077D-78DE-4CC0-9498-225DDC42E054" rel="noreferrer"><code>JSON_ARRAYAGG</code></a>.

<pre class="lang-sql prettyprint-override"><code>create table tab as
 select level col1, 'value '||level col2 from dual connect by level &lt;= 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;
</code></pre>

Result:

<pre class="lang-sql prettyprint-override"><code> RN JSON_DOC 
---------- ---------------------------------------------------------
 2 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}] 
</code></pre>

Test with large amount of data: 

<pre class="lang-sql prettyprint-override"><code>select rn, length (json_doc) json_size, json_doc from (
 &lt;query mentoined above here&gt;
 cross join (select dummy from dual connect by level &lt;= 1e5) 
 );

 RN JSON_SIZE JSON_DOC 
---------- ---------- ---------------------------------------------------------
 200000 5600001 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"},
</code></pre>

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

<hr>

In the release 19c the syntax of the the function <a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA" rel="noreferrer"><code>JSON_OBJECT</code> is simplified</a>. 
The query above will look now like this:

<pre><code>select json_arrayagg ( 
 json_object (*) returning clob 
) as json_doc 
from tab;
</code></pre>

On <a href="https://livesql.oracle.com/apex/livesql/s/irib428if19k68g0v78idhlq0" rel="noreferrer">Live SQL</a>.

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:

<pre><code>SELECT * from table AS JSON
</code></pre>

and receive a valid json similar to this: 

<pre><code>[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]
</code></pre>

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.

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

数据库

Python

JSON

背景我需要从Oracle获取几千行，并将它们转换为JSON以便在SlickGrid中使用。目前，我正在用PHP获取行，使用iconv将其从ISO转换为UTF-8，然后使用json_encode将其导出为json。整个操作在DB端大约需要1秒，生成JSON大约需要5秒。这太长了。 question我读到过Oracle12...

问在oracle 12c中以JSON形式返回sql查询的结果
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在oracle 12c中以JSON形式返回sql查询的结果EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在oracle 12c中以JSON形式返回sql查询的结果
EN