Oracle 12c에서 SQL 쿼리의 결과를 JSON으로 반환합니다.
배경
Oracle에서 수천 개의 행을 가져와 SlickGrid에서 사용할 수 있도록 JSON으로 변환해야 합니다.현재 저는 PHP에서 행을 가져와서 iconv를 사용하여 ISO에서 UTF-8로 변환하고 json_encode를 사용하여 json으로 내보내는 중입니다.전체 작업은 DB 측에서 약 1초, JSON을 생성하는 데 약 5초가 소요됩니다.너무 길어요.
그 질문은
오라클 12c가 JSON을 지원한다고 읽었는데 정확히 필요한 것을 찾을 수가 없습니다.
표준 SQL 쿼리 결과를 json 형식으로 반환할 수 있는 방법이 있습니까?
다음과 유사한 질문을 하고 싶습니다.
SELECT * from table AS JSON
다음과 유사한 유효한 json을 수신합니다.
[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]
중요한 것은 클라이언트 측에서 ISO-8859-2 문자 집합을 사용하고 JSON이 UTF-8에 있거나 시퀀스를 이스케이프해야 하기 때문에 유니코드 시퀀스를 이스케이프해야 한다는 것입니다.
Oracle 12c 버전 12.1.0.2(2014년 11월 11일 현재 최신 버전)에는 JSON 지원이 추가되었습니다. https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC
그것은 10월 17일부터 이용 가능합니다.https://blogs.oracle.com/db/entry/oracle_database_12c_release_1
해당 버전으로 패치/작업할 수 없다면 Lewis Cunningham과 Jonas Krogsboell이 작성한 훌륭한 패키지가 있습니다: PL/JSON * http://pljson.sourceforge.net/
훌륭한 패키지입니다(여러 데이터베이스 설치에서 사용해 왔습니다).
포함된 예는 양호하며 대부분의 시나리오를 다룹니다.
declare
ret json;
begin
ret := json_dyn.executeObject('select * from tab');
ret.print;
end;
/
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"]
또는
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"}
릴리스 12.2에는 SQL 쿼리에서 직접 JSON 문서를 생성하는 새로운 기능이 포함되어 있습니다.목표를 달성하는 가장 쉬운 방법은 및 함수를 사용하는 것입니다.
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;
결과:
RN JSON_DOC
---------- ---------------------------------------------------------
2 [{"col1":1,"col2":"value 1"},{"col1":2,"col2":"value 2"}]
대량의 데이터를 사용하여 테스트:
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"},
느린 테스트 기계에서 5,6M JSON을 만드는 데 약 1초가 걸렸습니다.
릴리스 19c에서는 함수의 구문이 단순화됩니다.
위의 쿼리는 다음과 같습니다.
select json_arrayagg (
json_object (*) returning clob
) as json_doc
from tab;
라이브 SQL.
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
Emptable을 사용한 간단한 예:
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;
결과는 다음과 같습니다.
{"ROWSET": [
{
"EMPNO": 7839,
"ENAME": "KING",
"JOB": "PRESIDENT"
},
{
"EMPNO": 7698,
"ENAME": "BLAKE",
"JOB": "MANAGER"
},
[...]
{
"EMPNO": 7934,
"ENAME": "MILLER",
"JOB": "CLERK"
}
]}
Oracle 19c부터는 테이블 행에 대한 JSON 표현을 구성하는 구문이 단순화됩니다.
: 의 합니다.hr.employees하려면 json을 사용합니다.
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
...
JSON에 대한 Oracle 12c 지원은 JSON 개체를 저장하고 쿼리한 후 선택할 수 있는 기능입니다.
표 형식이 있고 데이터를 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/
SQL VARCHAR2 제한은 4000자입니다.
http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/ 도 볼 수 있지만 오라클 개체 유형이 성능을 향상시킬 것이라고는 생각하지 않습니다.
또 다른 방법은 XMLType을 사용하여 XML을 내보내는 것입니다.그런 다음 XML을 JSON으로 변환합니다.XMLType은 특수 문자를 처리하며 API는 상당히 안정적입니다(오라클 14용으로 프로그램을 다시 작성할 필요가 없습니다).
오라클 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;
사용해 보십시오.
:) 인생은 행복합니다.
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;
19C에서 테스트:
SQL> select JSON_OBJECT(*) from HR.EMPLOYEES;
------------------------------------------------------------------------------
{"ID":100,"FirstName":"Steven","LastName":"King", ...}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar", ...}
또는:
SQL> select json_arrayagg(JSON_OBJECT(*) returning clob ) from HR.EMPLOYEES;
------------------------------------------------------------------------------
[ {"ID":100,"FirstName":"Steven","LastName":"King", ...},{"ID":101,"FirstName":"Neena","LastName":"Kochhar", ...}]
Python 솔루션이 보이지 않습니다(JSON을 덤프해야 할 경우).
중간 크기 추출을 위해 json-ora-extract를 작성했습니다(데이터 세트가 사용 가능한 메모리에 맞아야 하기 때문).
그것은 사용합니다.wx_Oracle그리고.json 버전를 읽고 *.jsonjava.
압축 파일을 생성하는 옵션도 있습니다.*.gzjava.
언급URL : https://stackoverflow.com/questions/23560996/return-results-of-a-sql-query-as-json-in-oracle-12c
'source' 카테고리의 다른 글
| 어떻게 UBSan 보고서 gdb를 중단하고 계속할 수 있습니까? (0) | 2023.06.10 |
|---|---|
| 앱 스토어 링크: "이 앱 등급/검토" (0) | 2023.06.10 |
| C 다중 매크로의 사전 프로세서 테스트 정의 (0) | 2023.06.10 |
| VBA를 사용하여 Excel 시트를 현재 디렉토리에 저장 (0) | 2023.06.10 |
| 이 식은 호출할 수 없습니다.'번호' 유형에 호출 서명이 없습니다. (0) | 2023.06.10 |