在 oracle 12c 中将 sql 查询的结果作为 JSON 返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23560996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return results of a sql query as JSON in oracle 12c
提问by SWilk
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.
我需要从 Oracle 获取几千行并将它们转换为 JSON 以在 SlickGrid 中使用。目前我正在获取 PHP 中的行，使用 iconv 将其从 ISO 转换为 UTF-8，并使用 json_encode 导出到 json。整个操作在 DB 端大约需要 1 秒，生成 JSON 需要 5 秒。这是很长的路。
The question
问题
I have read that Oracle 12c supports JSON, but I cannot find exactly what I need.
我读过 Oracle 12c 支持 JSON，但我找不到我需要的确切内容。
Is there a way to return the result of a standard sql query in a json format?
有没有办法以json格式返回标准sql查询的结果？
supposedly I would like to issue a query similar to this:
据说我想发出类似这样的查询：
SELECT * from table AS JSON
and receive a valid json similar to this:
并接收类似于此的有效 json：
[{"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.
重要的是，我需要为我转义 unicode 序列，因为我在客户端使用 ISO-8859-2 字符集，而 JSON 必须采用 UTF-8 或对序列进行转义。
采纳答案by Olafur Tryggvason
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
Oracle 12c 版本 12.1.0.2（截至 11.11.2014 的最新版本）添加了 JSON 支持：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
它自 10 月 17 日起可用。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/
如果您无法修补/使用该版本，则可以使用 Lewis Cunningham 和 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;
/
回答by mark d drake
12cR2 (available in the Oracle Cloud) supports this natively.
12cR2（在 Oracle Cloud 中可用）本身支持这一点。
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"}
回答by PT_STAR
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:
您可以使用 xmltype 将 SQL 的结果转换为 XML 和 JSON。请参阅以下文章，了解自版本 9 起适用于 Oracle 的解决方案。您还可以下载包 itstar_xml_util：
http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies
http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies
A simple example with the emp table:
emp 表的一个简单示例：
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"
}
]}
回答by 0xdb
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_OBJECTand JSON_ARRAYAGG.
12.2 版包括直接从 SQL 查询生成 JSON 文档的新功能。实现目标的最简单方法是使用函数：JSON_OBJECT和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 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.
在慢速测试机器上花了大约 1 秒。创建 5,6M JSON。
In the release 19c the syntax of the the function JSON_OBJECTis simplified.
The query above will look now like this:
在 19c 版本中，函数的语法得到了JSON_OBJECT简化。
上面的查询现在看起来像这样：
select json_arrayagg (
json_object (*) returning clob
) as json_doc
from tab;
On Live SQL.
回答by Shahbaz Ali
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;
回答by Kaushik Nayak
Starting Oracle 19c, the syntax to construct a JSON representation for a row of a table is simplified
从 Oracle 19c 开始，简化了为表的一行构造 JSON 表示的语法
For Eg: To convert all the rows of the hr.employeesto separate jsons, use
例如：要将所有行转换hr.employees为单独的 jsons，请使用
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
...
回答by vav
Oracle 12c support for JSON is an ability to store JSON objects, query them and select from them.
Oracle 12c 对 JSON 的支持是一种存储 JSON 对象、查询它们并从中选择的能力。
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/
您有表格格式，只需要将您的数据显示为 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/
Just mind the SQL VARCHAR2 limit of 4000 characters.
请注意 4000 个字符的 SQL VARCHAR2 限制。
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.
您还可以查看http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/但我不认为，那个 oracle 对象类型会提高你的表现。
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).
另一种方法是使用 XMLType 导出 XML。然后将 XML 转换为 JSON。XMLType 将处理特殊字符，并且 API 非常稳定（您无需为 Oracle 14 重写您的程序）。
回答by Himanshu sharma
To add to the answer in oracle 12.2 , you can create json as you want like this .
要添加到 oracle 12.2 中的答案，您可以像这样根据需要创建 json。
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;
回答by olekb
I do not see Python solution (in case you need to dump JSON).
我没有看到 Python 解决方案（以防您需要转储 JSON）。
I wrote json-ora-extractfor medium size extracts (because data-set has to fit available memory).
我为中等大小的提取编写了json-ora-extract（因为数据集必须适合可用内存）。
It uses wx_Oracleand jsonPython modules to read data from Oracle data base (any version) and dump it into *.jsonfile.
它使用wx_Oracle和jsonPython模块读取从Oracle数据的基础上（任何版本）数据，并将其转储到*.json文件中。
There's also an option to create compressed *.gzfile.
还有一个选项可以创建压缩*.gz文件。
