本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 將 JSON Oracle 查詢轉換為 PostgreSQL 資料庫 SQL
*Pinesh Singal 和 Lokesh Gurram，Amazon Web Services*
## 總結
此從內部部署移至 Amazon Web Services (AWS) Cloud 的遷移程序會使用 AWS Schema Conversion Tool (AWS SCT)，將程式碼從 Oracle 資料庫轉換為 PostgreSQL 資料庫。AWS SCT 會自動轉換大部分的程式碼。不過，JSON 相關的 Oracle 查詢不會自動轉換。
從 Oracle 12.2 版本開始，Oracle Database 支援各種 JSON 函數，可協助將 JSON 型資料轉換為 ROW 型資料。不過，AWS SCT 不會自動將 JSON 型資料轉換為 PostgreSQL 支援的語言。
此遷移模式主要著重於使用 `JSON_OBJECT`、 `JSON_ARRAYAGG`和 等函數，將 JSON 相關的 Oracle 查詢`JSON_TABLE`從 Oracle 資料庫手動轉換為 PostgreSQL 資料庫。
## 先決條件和限制
**先決條件**
+ 作用中的 AWS 帳戶
+ 內部部署 Oracle 資料庫執行個體 （啟動和執行中）
+ 適用於 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS) 或 Amazon Aurora PostgreSQL 相容版本資料庫執行個體 （啟動和執行中）
**限制**
+ JSON 相關查詢需要固定的 `KEY`和 `VALUE` 格式。不使用該格式會傳回錯誤的結果。
+ 如果 JSON 結構中的任何變更在結果區段中新增新的 `KEY`和 `VALUE`對，則必須在 SQL 查詢中變更對應的程序或函數。
+ 舊版 Oracle 和 PostgreSQL 支援某些 JSON 相關函數，但功能較少。
**產品版本**
+ Oracle 資料庫 12.2 版及更新版本
+ Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容 9.5 版及更新版本
+ AWS SCT 最新版本 （使用 1.0.664 版測試）
## Architecture
**來源技術堆疊**
+ 版本為 19c 的 Oracle 資料庫執行個體
**目標技術堆疊**
+ 版本為 13 的 Amazon RDS for PostgreSQL 或 Aurora PostgreSQL 相容資料庫執行個體
**目標架構**
![\[描述遵循圖表。\]](http://docs.aws.amazon.com/zh_tw/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 相容資料庫執行個體上執行 .sql 檔案。
## 工具
**AWS 服務**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) 是一種全受管關聯式資料庫引擎，專為雲端而建置，並與 MySQL 和 PostgreSQL 相容。
+ [適用於 PostgreSQL 的 Amazon Relational Database Service (Amazon RDS)](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/) 是一種通用資料庫工具。
## 最佳實務
使用 `JSON_TABLE`函數時，Oracle 查詢的預設`CAST`類型為 。最佳實務也是在 PostgreSQL `CAST`中使用，使用兩倍大於字元 (`>>`)。
如需詳細資訊，請參閱*其他資訊*區段中的 *Postgres\$1SQL\$1Read\$1JSON*。
## 史詩
### 在 Oracle 和 PostgreSQL 資料庫中產生 JSON 資料
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 將 JSON 資料存放在 Oracle 資料庫中。 | 在 Oracle 資料庫中建立資料表，並將 JSON 資料存放在 `CLOB`欄中。 使用*額外資訊*區段中的 *Oracle\$1Table\$1Creation\$1Insert\$1Script*。 | 遷移工程師 |
| 將 JSON 資料存放在 PostgreSQL 資料庫中。 | 在 PostgreSQL 資料庫中建立資料表，並將 JSON 資料存放在 `TEXT`欄中。使用*其他資訊*區段中的 *Postgres\$1Table\$1Creation\$1Insert\$1Script*。 | 遷移工程師 |
### 將 JSON 轉換為 ROW 格式
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 轉換 Oracle 資料庫上的 JSON 資料。 | 撰寫 Oracle SQL 查詢，以 ROW 格式讀取 JSON 資料。如需詳細資訊和語法範例，請參閱*其他資訊*區段中的 *Oracle\$1SQL\$1Read\$1JSON*。 | 遷移工程師 |
| 轉換 PostgreSQL 資料庫上的 JSON 資料。 | 撰寫 PostgreSQL 查詢，以 ROW 格式讀取 JSON 資料。如需詳細資訊和語法範例，請參閱*其他資訊*區段中的 *Postgres\$1SQL\$1Read\$1JSON*。 | 遷移工程師 |
### 使用 SQL 查詢手動轉換 JSON 資料，並以 JSON 格式報告輸出
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 在 Oracle SQL 查詢上執行彙總和驗證。 | 若要手動轉換 JSON 資料，請對 Oracle SQL 查詢執行聯結、彙總和驗證，並以 JSON 格式報告輸出。在*其他資訊*區段中的 *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join* 下使用程式碼。[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/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/zh_tw/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 遷移工程師 |
### 將 Oracle 程序轉換為包含 JSON 查詢的 PostgreSQL 函數
| 任務 | Description | 所需的技能 |
| --- | --- | --- |
| 將 Oracle 程序中的 JSON 查詢轉換為資料列。 | 如需 Oracle 程序範例，請使用上一個 Oracle 查詢，以及*其他資訊*區段中的代碼 under*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)
## 其他資訊
若要將 JSON 程式碼從 Oracle 資料庫轉換為 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`對。下表提供`VALUE`從 JSON 文件讀取特定 的相關資訊。
|
|
| KEY | 用來取得 VALUE 的 HIERARCHY 或 PATH | VALUE |
| --- |--- |--- |
| `profileType` | `metadata` -> `profileType` | "P" |
| `positionId` | `data` -> `positionId` | "0100" |
| `accountNumber` | `data` -> 帳戶 -> `accountNumber` | 42000 |
在上表中， `KEY``profileType`是 `metadata` `VALUE`的 `KEY`。`KEY` `positionId` 是 `VALUE`的 `data` `KEY`。`KEY` `accountNumber` 是 `VALUE`的 `account` `KEY`，而 `account``KEY`是 `VALUE`的 `data` `KEY`。
*範例 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` 是 Oracle 中的內建函數，可將 JSON 資料轉換為資料列格式。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 會使用`HIERARCHY`模型`VALUE`從 JSON 讀取 `KEY` 和 。以下範例`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 ;
```
使用單一大於運算子 (`>`) 將傳回為該 `VALUE`定義的 `KEY`。例如，`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`，您必須使用雙大於運算子。如果您使用單一大於運算子，查詢會傳回`VALUE`已定義的 （例如 `KEY`： `positionId`和 `VALUE`：`"0100"`)。使用雙大於運算子 (`>>`) 將傳回為該值定義的實際值 `KEY`（例如 `KEY`： `positionId`和 `VALUE`：`0100`，不含雙引號）。
在上述情況下， `parentAccountNumber` 是 `CAST`的類型`INT`， `accountNumber` 是 `CAST`的類型`INT`， `businessUnitId` 是 `CAST`的類型`INT`， `positionId`是 的類型， 是 `CAST`的類型`VARCHAR`。
下表顯示查詢結果，說明單一大於運算子 (`>`) 和雙大於運算子 () 的角色`>>`。
在第一個資料表中，查詢使用單一大於運算子 (`>`)。每個資料欄都是 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_OBJECTAGG`、 `JSON_ARRAY`和 `JSON_ARRAYAGG`。
+ `JSON_OBJECT` 接受兩個參數： `KEY`和 `VALUE`。參數`KEY`本質上應為硬式編碼或靜態。`VALUE` 參數衍生自資料表輸出。
+ `JSON_ARRAYAGG` 接受 `JSON_OBJECT` 做為參數。這有助於將一組`JSON_OBJECT`元素分組為清單。例如，如果您的 `JSON_OBJECT` 元素有多個記錄 （資料集中的多個 `KEY`和 `VALUE`對）， 會`JSON_ARRAYAGG`附加資料集並建立清單。根據資料結構語言， `LIST`是元素群組。在此內容中， `LIST`是一組`JSON_OBJECT`元素。
下列範例顯示一個 `JSON_OBJECT` 元素。
```
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
```
下一個範例顯示兩個`JSON_OBJECT`元素，以方括號 () `LIST`表示`[ ]`。
```
[
{
"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`並將 ROW 層級資料`JSON_AGG`轉換為 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
}
}
]
}
```