기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.
# JSON Oracle 쿼리를 PostgreSQL 데이터베이스 SQL로 변환
*Pinesh Singal, Lokesh Gurram, Amazon Web Services*
## 요약
온프레미스에서 Amazon Web Services(AWS) 클라우드로 이전하기 위한 이 마이그레이션 프로세스는 AWS Schema Conversion Tool(AWS SCT)를 사용하여 Oracle 데이터베이스의 코드를 PostgreSQL 데이터베이스로 변환합니다. 대부분의 코드는 AWS SCT에 의해 자동으로 변환됩니다. 그러나 JSON 관련 Oracle 쿼리는 자동으로 변환되지 않습니다.
Oracle 12.2 버전부터 Oracle 데이터베이스는 JSON 기반 데이터를 행 기반 데이터로 변환하는 데 도움이 되는 다양한 JSON 함수를 지원합니다. 그러나 AWS SCT는 JSON 기반 데이터를 PostgreSQL에서 지원하는 언어로 자동 변환하지 않습니다.
이 마이그레이션 패턴은 주로 `JSON_OBJECT`, `JSON_ARRAYAGG` 및 `JSON_TABLE`와 같은 함수를 사용하는 JSON 관련 Oracle 쿼리를 Oracle 데이터베이스에서 PostgreSQL 데이터베이스로 수동으로 변환하는 데 중점을 둡니다.
## 사전 조건 및 제한 사항
**사전 조건 **
+ 활성 상태의 AWS 계정
+ 온프레미스 Oracle 데이터베이스 인스턴스(작동 및 실행 중)
+ Amazon Relational Database Service(RDS) for PostgreSQ 또는 Amazon Aurora PostgreSQL-Compatible Edition 데이터베이스 인스턴스(가동 및 실행 중)
**제한 사항 **
+ JSON 관련 쿼리에는 고정된 `KEY` 및 `VALUE` 형식이 필요합니다. 해당 형식을 사용하지 않으면 잘못된 결과가 반환됩니다.
+ JSON 구조를 변경하여 결과 섹션에 새 `KEY` 및 `VALUE` 쌍이 추가되는 경우 SQL 쿼리에서 해당 프로시저 또는 함수를 변경해야 합니다.
+ 일부 JSON 관련 함수는 이전 버전의 Oracle 및 PostgreSQL에서 지원되지만 기능이 더 적습니다.
**제품 버전**
+ Oracle 데이터베이스 버전 12.2 이상
+ Amazon RDS for PostgreSQL 또는 Aurora PostgreSQL 호환 버전 9.5 이상
+ AWS SCT 최신 버전(버전 1.0.664를 사용하여 테스트됨)
## 아키텍처
**소스 기술 스택 **
+ 버전 19c의 Oracle 데이터베이스 인스턴스
**대상 기술 스택 **
+ 버전 13의 Amazon RDS for PostgreSQL 또는 Aurora PostgreSQL-Compatible 데이터베이스 인스턴스
**대상 아키텍처 **
![\[설명은 다이어그램을 따릅니다.\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png)
1. AWS SCT를 JSON 함수 코드와 함께 사용하여 소스 코드를 Oracle에서 PostgreSQL로 변환합니다.
1. 변환 과정에서 PostgreSQL이 지원하는 마이그레이션된 .sql 파일이 생성됩니다.
1. 변환되지 않은 Oracle JSON 함수 코드를 PostgreSQL JSON 함수 코드로 수동으로 변환합니다.
1. 대상 Aurora PostgreSQL 호환 DB 인스턴스에서.sql 파일을 실행합니다.
## 도구
**서비스**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html)는 클라우드용으로 구축되었으며 MySQL 및 PostgreSQL과 호환되는 완전 관리형 관계형 데이터베이스 엔진입니다.
+ [Amazon Relational Database Service(Amazon RDS) for PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html)은 AWS 클라우드에서 PostgreSQL 관계형 데이터베이스를 설정, 운영 및 조정하는 데 도움이 됩니다.
+ [AWS Schema Conversion Tool(AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)은 소스 데이터베이스 스키마와 대부분의 사용자 지정 코드를 대상 데이터베이스와 호환되는 형식으로 자동 변환하여 이기종 데이터베이스 마이그레이션을 지원합니다.
**기타 서비스**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html)는 기존 배포와 클라우드 기반 배포 모두에서 Oracle 데이터베이스의 개발 및 관리를 간소화하는 통합 개발 환경입니다.
+ PGAdmin 또는 DBeaver. [pgAdmin](https://www.pgadmin.org/)은 PostgreSQL을 위한 오픈 소스 관리 도구입니다. 데이터베이스 객체를 생성, 유지 관리 및 사용하는 데 도움이 되는 그래픽 인터페이스를 제공합니다. [DBeaver](https://dbeaver.io/)는 유니버설 데이터베이스 도구입니다.
## 모범 사례
Oracle 쿼리는`JSON_TABLE` 함수를 사용할 때 `CAST` 유형을 기본값으로 사용합니다. 두 번 큰 문자(`>>`)를 사용하여 PostgreSQL의 `CAST`도 사용하는 것이 가장 좋습니다.
자세한 내용은 *추가 정보* 섹션의 *Postgres\$1SQL\$1Read\$1JSON*을 참조하세요.
## 에픽
### Oracle 및 PostgreSQL 데이터베이스에서 JSON 데이터를 생성합니다.
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| JSON 데이터를 Oracle 데이터베이스에 저장합니다. | Oracle 데이터베이스에 테이블을 생성하고 `CLOB` 열에 JSON 데이터를 저장합니다. *추가 정보* 섹션에 있는 *Oracle\$1Table\$1Creation\$1Insert\$1Script*를 사용합니다. | 마이그레이션 엔지니어 |
| JSON 데이터를 PostgreSQL 데이터베이스에 저장합니다. | PostgreSQL 데이터베이스에서 테이블을 생성하고 `TEXT` 열에 JSON 데이터를 저장합니다. *추가 정보* 섹션에 있는 *Postgres\$1Table\$1Creation\$1Insert\$1Script*를 사용합니다. | 마이그레이션 엔지니어 |
### JSON을 ROW 형식으로 변환
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| Oracle 데이터베이스의 JSON 데이터를 변환합니다. | Oracle SQL 쿼리를 작성하여 JSON 데이터를 ROW 형식으로 읽어 들입니다. 자세한 내용 및 예제 구문은 *추가 정보* 섹션의 *Oracle\$1SQL\$1Read\$1JSON*을 참조하세요. | 마이그레이션 엔지니어 |
| PostgreSQL 데이터베이스의 JSON 데이터를 변환합니다. | PostgreSQL 쿼리를 작성하여 JSON 데이터를 ROW 형식으로 읽어 들입니다. 자세한 내용 및 예제 구문은 *추가 정보* 섹션의 *Postgres\$1SQL\$1Read\$1JSON*을 참조하세요. | 마이그레이션 엔지니어 |
### SQL 쿼리를 사용하여 JSON 데이터를 수동으로 변환하고 출력을 JSON 형식으로 보고합니다.
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| Oracle SQL 쿼리에 대한 집계 및 검증을 수행합니다. | JSON 데이터를 수동으로 변환하려면 Oracle SQL 쿼리에서 조인, 집계 및 검증을 수행하고 출력을 JSON 형식으로 보고하세요. *추가 정보* 섹션의 *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join* 아래에 있는 코드를 사용합니다.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 마이그레이션 엔지니어 |
| Postgres SQL 쿼리에 대한 집계 및 검증을 수행합니다. | JSON 데이터를 수동으로 변환하려면 PostgreSQL 쿼리에서 조인, 집계 및 검증을 수행하고 출력을 JSON 형식으로 보고하세요. *추가 정보* 섹션의 *Postgres\$1SQL\$1JSON\$1Aggregation\$1Join* 아래에 있는 코드를 사용합니다.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/ko_kr/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 마이그레이션 엔지니어 |
### 오라클 프로시저를 JSON 쿼리가 포함된 PostgreSQL 함수로 변환
| 작업 | 설명 | 필요한 기술 |
| --- | --- | --- |
| Oracle 프로시저의 JSON 쿼리를 행으로 변환합니다. | 예제 Oracle 프로시저의 경우 이전 Oracle 쿼리와 *추가 정보* 섹션의 *Oracle\$1procedure\$1with\$1JSON\$1Query*에 있는 코드를 사용하세요. | 마이그레이션 엔지니어 |
| JSON 쿼리가 있는 PostgreSQL 함수를 행 기반 데이터로 변환합니다. | 예제 PostgreSQL 함수의 경우 *추가 정보* 섹션의 PostgreSQL 쿼리와 *Postgres\$1function\$1with\$1JSON\$1Query*에 있는 코드를 사용하세요. | 마이그레이션 엔지니어 |
## 관련 리소스
+ [Oracle JSON 함수](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html)
+ [PostgreSQL JSON 함수](https://www.postgresql.org/docs/13/functions-json.html)
+ [Oracle JSON 함수 예제](https://oracle-base.com/articles/12c/sql-json-functions-12cr2)
+ [PostgreSQL JSON 함수 예제](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg)
+ [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
## 추가 정보
Oracle 데이터베이스의 JSON 코드를 PostgreSQL 데이터베이스로 변환하려면 다음 스크립트를 순서대로 사용하세요.
**1: Oracle\$1Table\$1Creation\$1Insert\$1Script**
```
create table aws_test_table(id number,created_on date default sysdate,modified_on date,json_doc clob);
REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc)
values (1,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{
"metadata" : {
"upperLastNameFirstName" : "ABC XYZ",
"upperEmailAddress" : "abc@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "032323323",
"displayName" : "Abc, Xyz",
"firstName" : "Xyz",
"lastName" : "Abc",
"emailAddress" : "abc@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0100",
"arrayPattern" : " -'",
"a]')
|| TO_CLOB(q'[ccount" : {
"companyId" : "SMGE",
"businessUnitId" : 7,
"accountNumber" : 42000,
"parentAccountNumber" : 32000,
"firstName" : "john",
"lastName" : "doe",
"street1" : "retOdertcaShr ",
"city" : "new york",
"postalcode" : "XY ABC",
"country" : "United States"
},
"products" : [
{
"appUserGuid" : "i0acc4450000001823fbad478e2eab8a0",
"id" : "0000000046",
]')
|| TO_CLOB(q'[ "name" : "ProView",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}]'));
Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc) values (2,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{
"metadata" : {
"upperLastNameFirstName" : "PQR XYZ",
"upperEmailAddress" : "pqr@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "54534343",
"displayName" : "Xyz, pqr",
"firstName" : "pqr",
"lastName" : "Xyz",
"emailAddress" : "pqr@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0090",
"arrayPattern" : " -'",
"account" : {
"companyId" : "CARS",
"busin]')
|| TO_CLOB(q'[essUnitId" : 6,
"accountNumber" : 42001,
"parentAccountNumber" : 32001,
"firstName" : "terry",
"lastName" : "whitlock",
"street1" : "UO 123",
"city" : "TOTORON",
"region" : "NO",
"postalcode" : "LKM 111",
"country" : "Canada"
},
"products" : [
{
"appUserGuid" : "ia744d7790000016899f8cf3f417d6df6",
"id" : "0000000014",
"name" : "ProView eLooseleaf",
]')
|| TO_CLOB(q'[ "domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}]'));
commit;
```
**2. Postgres\$1Table\$1Creation\$1Insert\$1Script**
```
create table aws_test_pg_table(id int,created_on date ,modified_on date,json_doc text);
insert into aws_test_pg_table(id,created_on,modified_on,json_doc)
values(1,now(),now(),'{
"metadata" : {
"upperLastNameFirstName" : "ABC XYZ",
"upperEmailAddress" : "abc@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "032323323",
"displayName" : "Abc, Xyz",
"firstName" : "Xyz",
"lastName" : "Abc",
"emailAddress" : "abc@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0100",
"arrayPattern" : " -",
"account" : {
"companyId" : "SMGE",
"businessUnitId" : 7,
"accountNumber" : 42000,
"parentAccountNumber" : 32000,
"firstName" : "john",
"lastName" : "doe",
"street1" : "retOdertcaShr ",
"city" : "new york",
"postalcode" : "XY ABC",
"country" : "United States"
},
"products" : [
{
"appUserGuid" : "i0acc4450000001823fbad478e2eab8a0",
"id" : "0000000046",
"name" : "ProView",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}');
insert into aws_test_pg_table(id,created_on,modified_on,json_doc)
values(2,now(),now(),'{
"metadata" : {
"upperLastNameFirstName" : "PQR XYZ",
"upperEmailAddress" : "pqr@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "54534343",
"displayName" : "Xyz, pqr",
"firstName" : "pqr",
"lastName" : "Xyz",
"emailAddress" : "a*b**@h**.k**",
"productRegistrationStatus" : "Not registered",
"positionId" : "0090",
"arrayPattern" : " -",
"account" : {
"companyId" : "CARS",
"businessUnitId" : 6,
"accountNumber" : 42001,
"parentAccountNumber" : 32001,
"firstName" : "terry",
"lastName" : "whitlock",
"street1" : "UO 123",
"city" : "TOTORON",
"region" : "NO",
"postalcode" : "LKM 111",
"country" : "Canada"
},
"products" : [
{
"appUserGuid" : "ia744d7790000016899f8cf3f417d6df6",
"id" : "0000000014",
"name" : "ProView eLooseleaf",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}');
```
**3. Oracle\$1SQL\$1Read\$1JSON**
다음 코드 블록은 Oracle JSON 데이터를 행 형식으로 변환하는 방법을 보여줍니다.
*쿼리 및 구문 예제*
```
SELECT JSON_OBJECT(
'accountCounts' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'businessUnitId' VALUE business_unit_id,
'parentAccountNumber' VALUE parent_account_number,
'accountNumber' VALUE account_number,
'totalOnlineContactsCount' VALUE online_contacts_count,
'countByPosition' VALUE
JSON_OBJECT(
'taxProfessionalCount' VALUE tax_count,
'attorneyCount' VALUE attorney_count,
'nonAttorneyCount' VALUE non_attorney_count,
'clerkCount' VALUE clerk_count
) ) ) ) FROM
(SELECT tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count,
SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count,
SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count,
SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count
FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS (
parent_account_number NUMBER PATH
'$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' )
) AS tab_data
INNER JOIN JSON_TABLE ( '{
"accounts": [{
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}, {
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}]
}', '$.accounts[*]' ERROR ON ERROR
COLUMNS (
parent_account_number PATH '$.parentAccountNumber',
account_number PATH '$.accountNumber',
business_unit_id PATH '$.businessUnitId')
) static_data
ON ( static_data.parent_account_number = tab_data.parent_account_number
AND static_data.account_number = tab_data.account_number
AND static_data.business_unit_id = tab_data.business_unit_id )
GROUP BY
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number );
```
JSON 문서는 데이터를 컬렉션으로 저장합니다. 각 컬렉션에는 `KEY` 및 `VALUE` 쌍이 있을 수 있습니다. 모든 `VALUE`는 `KEY` 중첩과 `VALUE` 쌍을 가질 수 있습니다. 다음 표는 JSON 문서의 `VALUE`에서 특정 내용을 읽는 방법에 대한 정보를 제공합니다.
|
|
| 키 | 값을 가져오는 데 사용할 계층 또는 경로 | 값 |
| --- |--- |--- |
| `profileType` | `metadata` -> `profileType` | "P" |
| `positionId` | `data` -> `positionId` | "0100" |
| `accountNumber` | `data` -> 계정 -> `accountNumber` | 42000 |
이전 표에서는 `KEY` `profileType`이 `metadata` `KEY`의 `VALUE`입니다. `KEY` `positionId`는 `data` `KEY`의 `VALUE`입니다. `KEY` `accountNumber`는 `account` `KEY`의 `VALUE`이고, `account` `KEY`는 `data` `KEY`의 `VALUE`입니다.
*예제 JSON 문서*
```
{
"metadata" : {
"upperLastNameFirstName" : "ABC XYZ",
"upperEmailAddress" : "abc@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "032323323",
"displayName" : "Abc, Xyz",
"firstName" : "Xyz",
"lastName" : "Abc",
"emailAddress" : "abc@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0100",
"arrayPattern" : " -",
"account" : {
"companyId" : "SMGE",
"businessUnitId" : 7,
"accountNumber" : 42000,
"parentAccountNumber" : 32000,
"firstName" : "john",
"lastName" : "doe",
"street1" : "retOdertcaShr ",
"city" : "new york",
"postalcode" : "XY ABC",
"country" : "United States"
},
"products" : [
{
"appUserGuid" : "i0acc4450000001823fbad478e2eab8a0",
"id" : "0000000046",
"name" : "ProView",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}
```
*JSON 문서에서 선택한 필드를 가져오는 데 사용되는 SQL 쿼리*
```
select parent_account_number,account_number,business_unit_id,position_id from aws_test_table aws,JSON_TABLE ( json_doc, '$' ERROR ON ERROR
COLUMNS (
parent_account_number NUMBER PATH '$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId'
)) as sc
```
이전 쿼리에서는 `JSON_TABLE`은 JSON 데이터를 행 형식으로 변환하는 Oracle의 기본 제공 함수입니다. JSON\$1TABLE 함수에는 JSON 형식의 파라미터가 예상됩니다.
`COLUMNS`의 모든 항목에는 `PATH`가 미리 정의되어 있으며 주어진 `KEY`에 적합한 `VALUE`가 행 형식으로 반환됩니다.
*이전 쿼리의 결과*
|
|
| PARENT\$1ACCOUNT\$1NUMBER | ACCOUNT\$1NUMBER | BUSINESS\$1UNIT\$1ID | POSITION\$1ID |
| --- |--- |--- |--- |
| 32000 | 42000 | 7 | 0100 |
| 32001 | 42001 | 6 | 0090 |
**4. Postgres\$1SQL\$1Read\$1JSON**
** ***쿼리 및 구문 예제*
```
select *
from (
select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber,
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber,
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId,
(json_doc::json->'data'->>'positionId')::VARCHAR as positionId
from aws_test_pg_table) d ;
```
Oracle에서 `PATH`는 특정 `KEY` 및 `VALUE`를 식별하는 데 사용됩니다. 그러나 PostgreSQL은 JSON에서 `KEY` 및 `VALUE`를 위한 `HIERARCHY` 모델을 사용합니다. `Oracle_SQL_Read_JSON`에 언급된 것과 동일한 JSON 데이터가 다음 예제에 사용됩니다.
*유형이 CAST인 SQL 쿼리는 허용되지 않습니다*
(`CAST`를 강제로 입력하면 구문 오류가 발생하여 쿼리가 실패합니다.)
```
select *
from (
select (json_doc::json->'data'->'account'->'parentAccountNumber') as parentAccountNumber,
(json_doc::json->'data'->'account'->'accountNumber')as accountNumber,
(json_doc::json->'data'->'account'->'businessUnitId') as businessUnitId,
(json_doc::json->'data'->'positionId')as positionId
from aws_test_pg_table) d ;
```
하나 이상의 연산자(`>`)를 사용하면 해당 `KEY`에 대해 `VALUE`이 정의된 값이 반환됩니다. 예를 들어, `KEY`: `positionId` 및 `VALUE`: `"0100"`입니다.
하나 이상의 연산자(`>`)를 사용할 때는 유형 `CAST`를 사용할 수 없습니다.
*유형이 CAST인 SQL 쿼리는 허용됩니다*
```
select *
from (
select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber,
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber,
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId,
(json_doc::json->'data'->>'positionId')::varchar as positionId
from aws_test_pg_table) d ;
```
`CAST`유형을 사용하려면 double greater-than 연산자를 사용해야 합니다. 하나 이상의 큰 연산자를 사용하는 경우 쿼리는 정의된 `VALUE`(예: `KEY`는 `positionId` 및 `VALUE`는 `"0100"`)를 반환합니다. 두 배 큰 연산자(`>>`)를 사용하면 `KEY` (예: `KEY`: `positionId` 및 `VALUE`: `0100`, 큰따옴표 제외)에 대해 정의된 실제 값이 반환됩니다.
이전 케이스에서 `parentAccountNumber`은 `INT`에 대한 `CAST` 타입, `accountNumber`은 `INT`에 대한 `CAST` 타입, `businessUnitId`는 `INT`에 대한 `CAST` 타입, `positionId`는 `VARCHAR`에 대한 `CAST` 타입입니다.
다음 표에는 단일 초과 연산자(`>`)와 두 배 큰 연산자(`>>`)의 역할을 설명하는 쿼리 결과가 나와 있습니다.
첫 번째 테이블 테이블의 쿼리는 단일 대보다 큰 연산자(`>`)를 사용합니다. 각 열은 JSON 유형이며 다른 데이터 유형으로 변환할 수 없습니다.
|
|
| parentAccountNumber | accountNumber | businessUnitId | positionId |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | "0100" |
| 2005284042 | 2005284042 | 6 | "0090" |
| 2000272719 | 2000272719 | 1 | "0100" |
두 번째 테이블에서 쿼리는 두 배 큰 연산자(`>>`)를 사용합니다. 각 열은 열 값을 기반으로 하는 `CAST` 유형을 지원합니다. 예를 들어 이 경우에는 `INTEGER`입니다.
|
|
| parentAccountNumber | accountNumber | businessUnitId | positionId |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | 0100 |
| 2005284042 | 2005284042 | 6 | 0090 |
| 2000272719 | 2000272719 | 1 | 0100 |
**5. Oracle\$1SQL\$1JSON\$1Aggregation\$1Join**
*쿼리 예제*
```
SELECT
JSON_OBJECT(
'accountCounts' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'businessUnitId' VALUE business_unit_id,
'parentAccountNumber' VALUE parent_account_number,
'accountNumber' VALUE account_number,
'totalOnlineContactsCount' VALUE online_contacts_count,
'countByPosition' VALUE
JSON_OBJECT(
'taxProfessionalCount' VALUE tax_count,
'attorneyCount' VALUE attorney_count,
'nonAttorneyCount' VALUE non_attorney_count,
'clerkCount' VALUE clerk_count
) ) ) )
FROM
(SELECT
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count,
SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count,
SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count,
SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count
FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR COLUMNS (
parent_account_number NUMBER PATH
'$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' )
) AS tab_data
INNER JOIN JSON_TABLE ( '{
"accounts": [{
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}, {
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}]
}', '$.accounts[*]' ERROR ON ERROR
COLUMNS (
parent_account_number PATH '$.parentAccountNumber',
account_number PATH '$.accountNumber',
business_unit_id PATH '$.businessUnitId')
) static_data
ON ( static_data.parent_account_number = tab_data.parent_account_number
AND static_data.account_number = tab_data.account_number
AND static_data.business_unit_id = tab_data.business_unit_id )
GROUP BY
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number
);
```
행 수준 데이터를 JSON 형식으로 변환하기 위해 Oracle에는 `JSON_OBJECT`, `JSON_ARRAY`, `JSON_OBJECTAGG`, `JSON_ARRAYAGG`와 같은 빌트인 함수가 있습니다.
+ `JSON_OBJECT`는 `KEY` 및 `VALUE`라는 두 개의 파라미터를 허용합니다. `KEY` 파라미터는 하드코딩되거나 정적이어야 합니다. `VALUE` 파라미터는 테이블 출력에서 파생됩니다.
+ `JSON_ARRAYAGG`은 `JSON_OBJECT`를 파라미터로 받아들입니다. 이렇게 하면 `JSON_OBJECT` 요소 세트를 목록으로 그룹화하는 데 도움이 됩니다. 예를 들어 여러 레코드(데이터 세트에 있는 여러 개의 `KEY` 및 `VALUE` 페어)가 있는 `JSON_OBJECT` 요소가 있는 경우 `JSON_ARRAYAGG`는 데이터 세트를 추가하고 목록을 생성합니다. 데이터 구조 언어에 따르면 `LIST`는 요소 그룹입니다. 이 컨텍스트에서는 `LIST`는 `JSON_OBJECT` 요소 그룹입니다.
다음 예에서는 `JSON_OBJECT` 요소 하나를 보여 줍니다.
```
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
```
다음 예제에서는 대괄호(`[ ]`)로 표시된 `LIST`가 있는 두 `JSON_OBJECT` 요소를 보여줍니다.
```
[
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
,
{
"taxProfessionalCount": 2,
"attorneyCount": 1,
"nonAttorneyCount": 3,
"clerkCount":4
}
]
```
*예제 SQL 쿼리*
```
SELECT
JSON_OBJECT(
'accountCounts' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'businessUnitId' VALUE business_unit_id,
'parentAccountNumber' VALUE parent_account_number,
'accountNumber' VALUE account_number,
'totalOnlineContactsCount' VALUE online_contacts_count,
'countByPosition' VALUE
JSON_OBJECT(
'taxProfessionalCount' VALUE tax_count,
'attorneyCount' VALUE attorney_count,
'nonAttorneyCount' VALUE non_attorney_count,
'clerkCount' VALUE clerk_count
)
)
)
)
FROM
(SELECT
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END
) tax_count,
SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END
) attorney_count,
SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END
) non_attorney_count,
SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END
) clerk_count
FROM
aws_test_table scco, JSON_TABLE ( json_doc, '$' ERROR ON ERROR
COLUMNS (
parent_account_number NUMBER PATH '$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' )
) AS tab_data
INNER JOIN JSON_TABLE ( '{
"accounts": [{
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}, {
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}]
}', '$.accounts[*]' ERROR ON ERROR
COLUMNS (
parent_account_number PATH '$.parentAccountNumber',
account_number PATH '$.accountNumber',
business_unit_id PATH '$.businessUnitId')
) static_data ON ( static_data.parent_account_number = tab_data.parent_account_number
AND static_data.account_number = tab_data.account_number
AND static_data.business_unit_id = tab_data.business_unit_id )
GROUP BY
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number
);
```
*이전 SQL 쿼리의 출력 예제*
```
{
"accountCounts": [
{
"businessUnitId": 6,
"parentAccountNumber": 32001,
"accountNumber": 42001,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
},
{
"businessUnitId": 7,
"parentAccountNumber": 32000,
"accountNumber": 42000,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 1,
"nonAttorneyCount": 0,
"clerkCount": 0
}
}
]
}
```
**6. Postgres\$1SQL\$1JSON\$1Aggregation\$1Join**
PostgreSQL 빌트인 함수 `JSON_BUILD_OBJECT` 및 `JSON_AGG`는 ROW 수준 데이터를 JSON 형식으로 변환합니다. PostgreSQL `JSON_BUILD_OBJECT` 및 `JSON_AGG` 는 Oracle `JSON_OBJECT` 및 `JSON_ARRAYAGG`와 동일합니다.
*쿼리 예제*
```
select
JSON_BUILD_OBJECT ('accountCounts',
JSON_AGG(
JSON_BUILD_OBJECT ('businessUnitId',businessUnitId
,'parentAccountNumber',parentAccountNumber
,'accountNumber',accountNumber
,'totalOnlineContactsCount',online_contacts_count,
'countByPosition',
JSON_BUILD_OBJECT (
'taxProfessionalCount',tax_professional_count
,'attorneyCount',attorney_count
,'nonAttorneyCount',non_attorney_count
,'clerkCount',clerk_count
)
)
)
)
from (
with tab as (select * from (
select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber,
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber,
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId,
(json_doc::json->'data'->>'positionId')::varchar as positionId
from aws_test_pg_table) a ) ,
tab1 as ( select
(json_array_elements(b.jc -> 'accounts') ->> 'accountNumber')::integer accountNumber,
(json_array_elements(b.jc -> 'accounts') ->> 'businessUnitId')::integer businessUnitId,
(json_array_elements(b.jc -> 'accounts') ->> 'parentAccountNumber')::integer parentAccountNumber
from (
select '{
"accounts": [{
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}, {
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}]
}'::json as jc) b)
select
tab.businessUnitId::text,
tab.parentAccountNumber::text,
tab.accountNumber::text,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab.positionId::text = '0095' THEN 1 ELSE 0 END) tax_professional_count,
SUM(CASE WHEN tab.positionId::text = '0100' THEN 1 ELSE 0 END) attorney_count,
SUM(CASE WHEN tab.positionId::text = '0090' THEN 1 ELSE 0 END) non_attorney_count,
SUM(CASE WHEN tab.positionId::text = '0050' THEN 1 ELSE 0 END) clerk_count
from tab1,tab
where tab.parentAccountNumber::INTEGER=tab1.parentAccountNumber::INTEGER
and tab.accountNumber::INTEGER=tab1.accountNumber::INTEGER
and tab.businessUnitId::INTEGER=tab1.businessUnitId::INTEGER
GROUP BY tab.businessUnitId::text,
tab.parentAccountNumber::text,
tab.accountNumber::text) a;
```
*이전 쿼리의 예제 출력*
Oracle과 PostgreSQL의 출력은 정확히 동일합니다.
```
{
"accountCounts": [
{
"businessUnitId": 6,
"parentAccountNumber": 32001,
"accountNumber": 42001,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
},
{
"businessUnitId": 7,
"parentAccountNumber": 32000,
"accountNumber": 42000,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 1,
"nonAttorneyCount": 0,
"clerkCount": 0
}
}
]
}
```
**7.Oracle\$1procedure\$1with\$1JSON\$1Query**
이 코드는 Oracle 절차를 JSON SQL 쿼리가 있는 PostgreSQL 함수로 변환합니다. 쿼리가 JSON을 행으로, 그 반대로 변환하는 방법을 보여줍니다.
```
CREATE OR REPLACE PROCEDURE p_json_test(p_in_accounts_json IN varchar2, p_out_accunts_json OUT varchar2)
IS
BEGIN
/*
p_in_accounts_json paramter should have following format:
{
"accounts": [{
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}, {
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}]
}
*/
SELECT
JSON_OBJECT(
'accountCounts' VALUE JSON_ARRAYAGG(
JSON_OBJECT(
'businessUnitId' VALUE business_unit_id,
'parentAccountNumber' VALUE parent_account_number,
'accountNumber' VALUE account_number,
'totalOnlineContactsCount' VALUE online_contacts_count,
'countByPosition' VALUE
JSON_OBJECT(
'taxProfessionalCount' VALUE tax_count,
'attorneyCount' VALUE attorney_count,
'nonAttorneyCount' VALUE non_attorney_count,
'clerkCount' VALUE clerk_count
) ) ) )
into p_out_accunts_json
FROM
(SELECT
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab_data.position_id = '0095' THEN 1 ELSE 0 END) tax_count,
SUM(CASE WHEN tab_data.position_id = '0100' THEN 1 ELSE 0 END) attorney_count,
SUM(CASE WHEN tab_data.position_id = '0090' THEN 1 ELSE 0 END) non_attorney_count,
SUM(CASE WHEN tab_data.position_id = '0050' THEN 1 ELSE 0 END) clerk_count
FROM aws_test_table scco,JSON_TABLE ( json_doc, '$' ERROR ON ERROR
COLUMNS (
parent_account_number NUMBER PATH '$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId' )
) AS tab_data
INNER JOIN JSON_TABLE ( p_in_accounts_json, '$.accounts[*]' ERROR ON ERROR
COLUMNS (
parent_account_number PATH '$.parentAccountNumber',
account_number PATH '$.accountNumber',
business_unit_id PATH '$.businessUnitId')
) static_data
ON ( static_data.parent_account_number = tab_data.parent_account_number
AND static_data.account_number = tab_data.account_number
AND static_data.business_unit_id = tab_data.business_unit_id )
GROUP BY
tab_data.business_unit_id,
tab_data.parent_account_number,
tab_data.account_number
);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'Error while running the JSON query');
END;
/
```
*프로시저 실행*
다음 코드 블록은 프로시저에 대한 예제 JSON 입력을 사용하여 이전에 만든 Oracle 프로시저를 실행하는 방법을 설명합니다. 또한 이 프로시저의 결과 또는 출력도 제공합니다.
```
set serveroutput on;
declare
v_out varchar2(30000);
v_in varchar2(30000):= '{
"accounts": [{
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}, {
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}]
}';
begin
p_json_test(v_in,v_out);
dbms_output.put_line(v_out);
end;
/
```
*프로시저 출력*
```
{
"accountCounts": [
{
"businessUnitId": 6,
"parentAccountNumber": 32001,
"accountNumber": 42001,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
},
{
"businessUnitId": 7,
"parentAccountNumber": 32000,
"accountNumber": 42000,
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 1,
"nonAttorneyCount": 0,
"clerkCount": 0
}
}
]
}
```
**8.Postgres\$1function\$1with\$1JSON\$1Query**
*함수 예제*
```
CREATE OR REPLACE FUNCTION f_pg_json_test(p_in_accounts_json text)
RETURNS text
LANGUAGE plpgsql
AS
$$
DECLARE
v_out_accunts_json text;
BEGIN
SELECT
JSON_BUILD_OBJECT ('accountCounts',
JSON_AGG(
JSON_BUILD_OBJECT ('businessUnitId',businessUnitId
,'parentAccountNumber',parentAccountNumber
,'accountNumber',accountNumber
,'totalOnlineContactsCount',online_contacts_count,
'countByPosition',
JSON_BUILD_OBJECT (
'taxProfessionalCount',tax_professional_count
,'attorneyCount',attorney_count
,'nonAttorneyCount',non_attorney_count
,'clerkCount',clerk_count
))))
INTO v_out_accunts_json
FROM (
WITH tab AS (SELECT * FROM (
SELECT (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER AS parentAccountNumber,
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER AS accountNumber,
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER AS businessUnitId,
(json_doc::json->'data'->>'positionId')::varchar AS positionId
FROM aws_test_pg_table) a ) ,
tab1 AS ( SELECT
(json_array_elements(b.jc -> 'accounts') ->> 'accountNumber')::integer accountNumber,
(json_array_elements(b.jc -> 'accounts') ->> 'businessUnitId')::integer businessUnitId,
(json_array_elements(b.jc -> 'accounts') ->> 'parentAccountNumber')::integer parentAccountNumber
FROM (
SELECT p_in_accounts_json::json AS jc) b)
SELECT
tab.businessUnitId::text,
tab.parentAccountNumber::text,
tab.accountNumber::text,
SUM(1) online_contacts_count,
SUM(CASE WHEN tab.positionId::text = '0095' THEN 1 ELSE 0 END) tax_professional_count,
SUM(CASE WHEN tab.positionId::text = '0100' THEN 1 ELSE 0 END) attorney_count,
SUM(CASE WHEN tab.positionId::text = '0090' THEN 1 ELSE 0 END) non_attorney_count,
SUM(CASE WHEN tab.positionId::text = '0050' THEN 1 ELSE 0 END) clerk_count
FROM tab1,tab
WHERE tab.parentAccountNumber::INTEGER=tab1.parentAccountNumber::INTEGER
AND tab.accountNumber::INTEGER=tab1.accountNumber::INTEGER
AND tab.businessUnitId::INTEGER=tab1.businessUnitId::INTEGER
GROUP BY tab.businessUnitId::text,
tab.parentAccountNumber::text,
tab.accountNumber::text) a;
RETURN v_out_accunts_json;
END;
$$;
```
*함수 실행*
```
select f_pg_json_test('{
"accounts": [{
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}, {
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}]
}') ;
```
*함수 출력 *
다음 출력은 Oracle 프로시저 출력과 유사합니다. 차이점은 이 출력이 텍스트 형식이라는 점입니다.
```
{
"accountCounts": [
{
"businessUnitId": "6",
"parentAccountNumber": "32001",
"accountNumber": "42001",
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
},
{
"businessUnitId": "7",
"parentAccountNumber": "32000",
"accountNumber": "42000",
"totalOnlineContactsCount": 1,
"countByPosition": {
"taxProfessionalCount": 0,
"attorneyCount": 1,
"nonAttorneyCount": 0,
"clerkCount": 0
}
}
]
}
```