다양한 종류의 데이터를 해석하고 활용하는 일은 매우 중요합니다. 따라서 실제 데이터를 어떤 언어 및 플랫폼에서도 해석 가능한 형식으로 사용하는 것이 매우 중요합니다. JavaScript Object Notation(이하 JSON)이 개발자들 사이에서 인기를 얻게 된 이유도 이와 같습니다. 상대적으로 쉽게 작성할 수 있고, 처리 속도도 빠르며 파싱 없이 JavaScrpt에서 바로 사용할 수 있습니다.
이런 JSON 타입의 데이터를 처리하기 위해서는 다양한 방법이 존재합니다. 일반적으로 데이터베이스를 생각하시면 관계형 모델의 데이터를 다루는 관계형 데이터베이스를 떠올리실텐데, 데이터를 문서 모델 형태로 저장하는 데이터베이스를 다큐먼트 데이터베이스라고 하고JSON 데이터베이스도 한 종류에 해당됩니다. 개발자가 사전에 스키마를 계획 할 필요 없이 애플리케이션에서 사용하는 JSON 형식을 동일하게 데이터베이스에서 사용할 수 있기 때문에 많은 사랑을 받고 있습니다.
관계형 데이터베이스로 널리 알려진 오라클 데이터베이스도 컨버지드 데이터베이스 전략에 따라 많은 기능이 추가되었고 JSON을 포함한 다양한 데이터 포맷을 기존 데이터베이스 아키텍처를 활용하여 사용 가능합니다. 오라클 데이터베이스는 이전 버전부터 JSON 관련 다양한 기능들이 추가되었고 23c부터 네이티브 JSON 데이터 유형을 지원하여 JSON 데이터의 손쉬운 마이그레이션, 개발/관리를 가능합니다. 또한 클라우드 환경에서 Autonomous JSON Database라는 서비스명으로 오라클 데이터베이스의 이점과 서버리스 아키텍처의 장점을 활용하여 자동화된 프로비저닝, 확장/복구 기능을 제공하는 JSON 데이터베이스를 완전 관리형 플랫폼 서비스로 제공하고 있습니다.
이 글에서는 오라클데이터베이스 19c와 23c에서 JSON 데이터를 활용하는 방법을 예제로 알아보겠습니다. 이후 오라클데이터베이스 환경에서 제공되는 여러 JSON 관련 새로운 기능과 클라우드 환경에서 제공되는 Autonomous JSON Database(AJD)에 대해 계속해서 알아보도록 하겠습니다.
* Oracle Database 19c
- 19c에서는 JSON 데이터를 텍스트 방식으로 정의합니다. 일반적인 SQL 데이터유형인 VARCHAR2, CLOB, BLOB등을 이용해서 JSON 데이터를 저장하고 "is json"이라는 체크 제약조건을 명시해서 데이터가 JSON 처리에 적합한지 체크를 합니다.
기본적인 샘플은 아래와 같습니다.
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document VARCHAR2 (23767)
CONSTRAINT ensure_json CHECK (po_document IS JSON));
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date(SYSDATE,'YYYY-MM-DD'),
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" :
{"name" : "Alexis Bull",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" :
[{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date(SYSDATE,'YYYY-MM-DD'),
'{"PONumber" : 672,
"Reference" : "SBELL-20141017",
"Requestor" : "Sarah Bell",
"User" : "SBELL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Sarah Bell",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : "983-555-6509"},
"Special Instructions" : "Courier",
"LineItems" :
[{"ItemNumber" : 1,
"Part" : {"Description" : "Making the Grade",
"UnitPrice" : 20,
"UPCCode" : 27616867759},
"Quantity" : 8.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Nixon",
"UnitPrice" : 19.95,
"UPCCode" : 717951002396},
"Quantity" : 5},
{"ItemNumber" : 3,
"Part" : {"Description" : "Eric Clapton: Best Of 1981-1999",
"UnitPrice" : 19.95,
"UPCCode" : 75993851120},
"Quantity" : 5.0}]}');
commit;
조회 방법과 결과는 아래와 같습니다.
앞서 설명드린대로 SQL과 PL/SQL에서 지원되는 Function 이나 Condition을 이용해서 다양하게 JSON 데이터를 활용가능합니다. 이후에 상세하게 정리하겠습니다.
SELECT po.po_document.PONumber FROM j_purchaseorder po;
PONUMBER
--------------------------------------------------------
1600
672
SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
--------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]
983-555-6509
SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
---------------------------------------------------------
["Office","Mobile"]
* Oracle Database 23c
- 최신 버전인 23c에서는 네이티브 JSON 데이터타입을 지원합니다.
- 이전 19c에서 JSON 데이터를 텍스트 방식으로 저장할 때와 다르게 네이티브 JSON 데이터타입에서는 표준 JSON에서 지원되는 JSON scalr type(number, string, boolean, null)외에도 SQL scalar types도 확장 지원됩니다. ( binary, date, timestamp, ... )
같은 방식으로 테이블을 생성하고 데이터를 넣습니다. 단 po_docuement 컬럼을 JSON 형식으로 선언합니다.
CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
date_loaded TIMESTAMP (6) WITH TIME ZONE,
po_document JSON);
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date(SYSDATE,'YYYY-MM-DD'),
'{"PONumber" : 1600,
"Reference" : "ABULL-20140421",
"Requestor" : "Alexis Bull",
"User" : "ABULL",
"CostCenter" : "A50",
"ShippingInstructions" :
{"name" : "Alexis Bull",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : [{"type" : "Office", "number" : "909-555-7307"},
{"type" : "Mobile", "number" : "415-555-1234"}]},
"Special Instructions" : null,
"AllowPartialShipment" : true,
"LineItems" :
[{"ItemNumber" : 1,
"Part" : {"Description" : "One Magic Christmas",
"UnitPrice" : 19.95,
"UPCCode" : 13131092899},
"Quantity" : 9.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Lethal Weapon",
"UnitPrice" : 19.95,
"UPCCode" : 85391628927},
"Quantity" : 5.0}]}');
INSERT INTO j_purchaseorder
VALUES (
SYS_GUID(),
to_date(SYSDATE,'YYYY-MM-DD'),
'{"PONumber" : 672,
"Reference" : "SBELL-20141017",
"Requestor" : "Sarah Bell",
"User" : "SBELL",
"CostCenter" : "A50",
"ShippingInstructions" : {"name" : "Sarah Bell",
"Address" : {"street" : "200 Sporting Green",
"city" : "South San Francisco",
"state" : "CA",
"zipCode" : 99236,
"country" : "United States of America"},
"Phone" : "983-555-6509"},
"Special Instructions" : "Courier",
"LineItems" :
[{"ItemNumber" : 1,
"Part" : {"Description" : "Making the Grade",
"UnitPrice" : 20,
"UPCCode" : 27616867759},
"Quantity" : 8.0},
{"ItemNumber" : 2,
"Part" : {"Description" : "Nixon",
"UnitPrice" : 19.95,
"UPCCode" : 717951002396},
"Quantity" : 5},
{"ItemNumber" : 3,
"Part" : {"Description" : "Eric Clapton: Best Of 1981-1999",
"UnitPrice" : 19.95,
"UPCCode" : 75993851120},
"Quantity" : 5.0}]}');
commit;
기본적인 데이터 조회는 아래와 같습니다.
select po_document from j_purchaseorder ;
PO_DOCUMENT
---------------------------------------------------------------------------------------
{"PONumber":1600,"Reference":"ABULL-20140421","Requestor":"Alexis Bull","User":"ABULL","CostCenter":"A50","ShippingInstructions":{"name":"Alexis Bull","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":"CA","zipCode":99236,"country":"United States of America"},"Phone":[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]},"Special Instructions":null,"AllowPartialShipment":true,"LineItems":[{"ItemNumber":1,"Part":{"Description":"One Magic Christmas","UnitPrice":19.95,"UPCCode":13131092899},"Quantity":9},{"ItemNumber":2,"Part":{"Description":"Lethal Weapon","UnitPrice":19.95,"UPCCode":85391628927},"Quantity":5}]}
{"PONumber":672,"Reference":"SBELL-20141017","Requestor":"Sarah Bell","User":"SBELL","CostCenter":"A50","ShippingInstructions":{"name":"Sarah Bell","Address":{"street":"200 Sporting Green","city":"South San Francisco","state":"CA","zipCode":99236,"country":"United States of America"},"Phone":"983-555-6509"},"Special Instructions":"Courier","LineItems":[{"ItemNumber":1,"Part":{"Description":"Making the Grade","UnitPrice":20,"UPCCode":27616867759},"Quantity":8},{"ItemNumber":2,"Part":{"Description":"Nixon","UnitPrice":19.95,"UPCCode":717951002396},"Quantity":5},{"ItemNumber":3,"Part":{"Description":"Eric Clapton: Best Of 1981-1999","UnitPrice":19.95,"UPCCode":75993851120},"Quantity":5}]}
SELECT po.po_document.PONumber.number() FROM j_purchaseorder po;
PO.PO_DOCUMENT.PONUMBER.NUMBER()
--------------------------------
1600
672
SELECT json_value(po_document, '$.PONumber.number()') FROM j_purchaseorder;
JSON_VALUE(PO_DOCUMENT,'$.PONUMBER.NUMBER()')
---------------------------------------------
1600
672
SELECT po.po_document.ShippingInstructions.Phone FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
----------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]
"983-555-6509"
SELECT json_query(po_document, '$.ShippingInstructions.Phone') FROM j_purchaseorder;
JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE')
------------------------------------------------------------------------------------------
[{"type":"Office","number":"909-555-7307"},{"type":"Mobile","number":"415-555-1234"}]
"983-555-6509"
SELECT po.po_document.ShippingInstructions.Phone.type FROM j_purchaseorder po;
SHIPPINGINSTRUCTIONS
-----------------------------------------------------------------------------------------
["Office","Mobile"]
null
SELECT json_query(po_document, '$.ShippingInstructions.Phone.type' WITH WRAPPER) FROM j_purchaseorder;
JSON_QUERY(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS.PHONE.TYPE'WITHWRAPPER)
-----------------------------------------------------------------------------------------
["Office","Mobile"]
null
개인 시간을 투자하여 작성된 글로서, 글의 내용에 오류가 있을 수 있으며, 글 속의 의견은 개인적인 의견입니다
'3. 데이터관리' 카테고리의 다른 글
| OCI Data Integration 소개 - Data 통합을 위한 서버리스 ETL 서비스 (0) | 2024.02.28 |
|---|---|
| Oracle 데이터베이스 보안 솔루션 #1 - Overview (Database Security Solution Overview) (0) | 2024.02.27 |
| OCI DMS 사용하여 데이터 이관하기 (Online) (0) | 2024.02.22 |
| Select AI 쿼리 생성 기능 소개(ADB) (0) | 2024.02.20 |
| OCI DMS (Database Migration Service) 사용하여 데이터 이관하기 (0) | 2024.02.19 |
댓글