本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
將 JSON Oracle 查詢轉換為 PostgreSQL 資料庫 SQL
由 Pinesh Singal （AWS） 和 Lokesh Gurram （AWS） 建立
環境：PoC 或試行 | 來源：資料庫：關聯性 | 目標：Amazon RDS PostgreSQL |
R 類型：重新架構 | 工作負載：Oracle | 技術：資料庫；遷移 |
AWS 服務：Amazon Aurora；Amazon RDS |
Summary
此從內部部署遷移至 Amazon Web Services 的遷移程序 （AWS） Cloud 使用AWSSchema Conversion Tool （AWSSCT） 將程式碼從 Oracle 資料庫轉換為 PostgreSQL 資料庫。大部分程式碼都會由 AWS 自動轉換SCT。不過， JSON相關的 Oracle 查詢不會自動轉換。
從 Oracle 12.2 版開始，Oracle Database 支援各種JSON函數，可協助將 JSON型資料轉換為 ROW型資料。不過， AWS SCT 不會自動將 JSON型資料轉換為 Postgre 支援的語言SQL。
此遷移模式主要著重於使用 JSON_OBJECT、 JSON_ARRAYAGG和 等函數，將 JSON相關的 Oracle 查詢JSON_TABLE從 Oracle 資料庫手動轉換為 PostgreSQL 資料庫。
先決條件和限制
先決條件
作用中AWS帳戶
內部部署 Oracle 資料庫執行個體 （啟動和執行中）
適用於 PostgreSQL 的 Amazon Relational Database Service （Amazon RDS） 或 Amazon Aurora Postgre SQL-Compatible Edition 資料庫執行個體 （啟動和執行中）
限制
JSON相關查詢需要固定
KEYVALUE格式。不使用該格式會傳回錯誤的結果。如果JSON結構的任何變更在結果區段中新增了
KEY和VALUE對，則必須在SQL查詢中變更對應的程序或函數。舊版 Oracle 和 PostgreSQL 支援某些 JSON相關函數，但功能較少。
產品版本
Oracle 資料庫 12.2 版及更新版本
Amazon RDS for PostgreSQL 或 Aurora Postgre SQL- 相容 9.5 版及更新版本
AWS SCT 最新版本 （使用 1.0.664 版進行測試）
架構
來源技術堆疊
版本為 19c 的 Oracle 資料庫執行個體
目標技術堆疊
版本為 13 的 Amazon RDS for PostgreSQL 或 Aurora Postgre SQL相容資料庫執行個體
目標架構
AWS SCT 搭配 JSON 函數程式碼使用，將原始程式碼從 Oracle 轉換為 Postgre SQL。
轉換會產生 Postgre SQL支援的遷移 .sql 檔案。
手動將未轉換的 Oracle JSON函數代碼轉換為 PostgreSQL JSON函數代碼。
在目標 Aurora Postgre 相容SQL資料庫執行個體上執行 .sql 檔案。
工具
AWS 服務
Amazon Aurora 是完全受管的關聯式資料庫引擎，專為雲端打造，並與 MySQL 和 Postgre 相容SQL。
Postgre 的 Amazon Relational Database Service （Amazon RDS）SQL 可協助您在 AWS Cloud 中設定、操作和擴展 PostgreSQL 關聯式資料庫。
AWS Schema Conversion Tool （AWS SCT） 透過自動將來源資料庫結構描述和大部分自訂程式碼轉換為與目標資料庫相容的格式，支援異質資料庫遷移。
其他服務
Oracle SQL 開發人員
是一種整合的開發環境，可簡化傳統和雲端部署中 Oracle 資料庫的開發和管理。 pgAdmin 或 DBeaver。pgAdmin
是 Postgre 的開放原始碼管理工具SQL。它提供圖形界面，可協助您建立、維護和使用資料庫物件。DBeaver 是通用資料庫工具。
最佳實務
使用 JSON_TABLE函數時，Oracle 查詢的類型CAST為預設值。最佳實務也是在 PostgreSQL CAST中使用，使用大於兩倍的字元 （>>）。
如需詳細資訊，請參閱其他資訊區段中的 PostgresSQL_Read_JSON。
史詩
| 任務 | 描述 | 所需的技能 |
|---|---|---|
將JSON資料儲存在 Oracle 資料庫中。 | 在 Oracle 資料庫中建立資料表，並將JSON資料存放在 | 遷移工程師 |
將JSON資料儲存在 PostgreSQL 資料庫中。 | 在 PostgreSQL 資料庫中建立資料表，並將JSON資料存放在 | 遷移工程師 |
| 任務 | 描述 | 所需的技能 |
|---|---|---|
轉換 Oracle 資料庫上的JSON資料。 | 寫入 Oracle SQL查詢，以將JSON資料讀取為 ROW 格式。如需更多詳細資訊和語法範例，請參閱其他資訊區段中的 OracleSQL_Read_JSON。 | 遷移工程師 |
轉換 PostgreSQL 資料庫上的JSON資料。 | 撰寫 PostgreSQL 查詢，以將JSON資料讀取為 ROW 格式。如需更多詳細資訊和語法範例，請參閱其他資訊區段中的 PostgresSQL_Read_JSON。 | 遷移工程師 |
| 任務 | 描述 | 所需的技能 |
|---|---|---|
在 Oracle SQL查詢上執行彙總和驗證。 | 若要手動轉換JSON資料，請對 Oracle SQL查詢執行聯結、彙總和驗證，並以 JSON 格式報告輸出。在其他資訊區段中使用 Oracle_SQL_JSONAggregation_Join 下的程式碼。
| 遷移工程師 |
在 Postgres SQL查詢上執行彙總和驗證。 | 若要手動轉換JSON資料，請在 PostgreSQL 查詢上執行聯結、彙總和驗證，並以 JSON 格式報告輸出。在其他資訊區段中的 PostgresSQL_JSON__Aggregation_Join 下使用程式碼。
| 遷移工程師 |
| 任務 | 描述 | 所需的技能 |
|---|---|---|
將 Oracle 程序中的JSON查詢轉換為資料列。 | 在 Oracle 程序範例中，使用上一個 Oracle 查詢和其他資訊區段中 Oracle 查詢下代碼 Oracle_procedure_with_JSON_Query。 | 遷移工程師 |
將具有JSON查詢的 PostgreSQL 函數轉換為資料列型資料。 | 對於 PostgreSQL 函數範例，請使用先前的 PostgreSQL 查詢，以及其他資訊區段中 Postgres_function_withJSON_Query 下的程式碼。 | 遷移工程師 |
相關資源
其他資訊
若要將JSON程式碼從 Oracle 資料庫轉換為 PostgreSQL 資料庫，請依序使用下列指令碼。
1。Oracle_Table_Creation_Insert_Script
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_Table_Creation_Insert_Script
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_SQL_Read_JSON
下列程式碼區塊說明如何將 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 | HIERARCHY 或 PATH 用於取得 VALUE | VALUE |
|
| "P" |
|
| "0100" |
|
| 42000 |
在上一個資料表中， KEYprofileType是 metadata VALUE的 KEY。KEY positionId 是 VALUE的 data KEY。KEY accountNumber 是 account VALUE的 KEY，而 accountKEY是 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" } ] } }
SQL 查詢，用於從JSON文件中取得選取的欄位
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_TABLE 函數預期參數為 JSON 格式。
中的每個項目COLUMNS都有預先定義的 PATH，且指定的VALUE項目會以資料列格式KEY傳回。
上一個查詢的結果
PARENT_ACCOUNT_NUMBER | ACCOUNT_NUMBER | BUSINESS_UNIT_ID | POSITION_ID |
32000 | 42000 | 7 | 0100 |
32001 | 42001 | 6 | 0090 |
4. PostgresSQL_Read_JSON
查詢和語法範例
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從 讀取 KEY和 JSON。以下範例Oracle_SQL_Read_JSON使用 所述的相同JSON資料。
SQL CAST不允許 類型 的查詢
（如果您強制輸入 類型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不允許輸入 類型>。
SQL CAST 允許類型 的查詢
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_SQL_JSON_Aggregation_Join
查詢範例
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. PostgresSQL_JSON_Aggregation_Join
PostgreSQL 內建函數並將 ROW層級資料JSON_BUILD_OBJECTJSON_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_procedure_withJSON_Query
此程式碼會將 Oracle 程序轉換為具有JSONSQL查詢的 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; /
執行程序
下列程式碼區塊說明如何執行先前建立的 Oracle 程序，並JSON輸入程序的範例。它還為您提供此程序的結果或輸出。
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_function_withJSON_Query
範例函數
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 } } ] }
