翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。 # 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 Database は JSON ベースのデータを行ベースのデータに変換するのに役立つさまざまな JSON 関数をサポートしています。ただし、AWS SCT は JSON ベースのデータを PostgreSQL でサポートされている言語に自動的に変換しません。 この移行パターンは、主に、`JSON_OBJECT`、`JSON_ARRAYAGG`、`JSON_TABLE`などの関数を使用する JSON 関連の Oracle クエリを Oracle データベースから PostgreSQL データベースに手動で変換することに重点を置いています。 ## 前提条件と制限事項 **前提条件** + アクティブな AWS アカウント。 + オンプレミスの Oracle データベースインスタンス (稼働中) + PostgreSQL または Amazon Aurora PostgreSQL 互換エディションデータベースインスタンス (稼働中) の Amazon Relational Database Service (Amazon RDS) **制限事項** + JSON 関連のクエリには、固定の`KEY`と`VALUE`形式が必要です。この形式を使用しないと、間違った結果が返されます。 + JSON構造の変更により、結果セクションに新しい`KEY`と`VALUE`のペアが追加された場合、SQLクエリで対応するプロシージャまたは関数を変更する必要があります。 + JSON 関連の関数の中には、以前のバージョンの Oracle と PostgreSQL でサポートされていますが、機能が少ないものもあります。 **製品バージョン** + インメモリデータベース (バージョン 12.1 以降) + Amazon RDS for PostgreSQL または Aurora PostgreSQL 互換バージョン 9.5 以降 + AWS SCT 最新バージョン (バージョン 1.0.664 を使用してテスト済み) ## アーキテクチャ **ソーステクノロジースタック** + バージョン 19c の Oracle データベースインスタンス **ターゲットテクノロジースタック** + Amazon RDS for PostgreSQL または Aurora PostgreSQL 互換データベースインスタンス (バージョン 13) **ターゲットアーキテクチャ** ![\[説明は図のとおりです。\]](http://docs.aws.amazon.com/ja_jp/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 ファイルを実行します。 ## ツール **AWS サービス** + [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) はフルマネージド型のリレーショナルデータベースエンジンで、MySQL および PostgreSQL と互換性があります。 + [Amazon Relational Database Service (Amazon RDS)](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) を使用して、AWS クラウドでリレーショナルデータベース (DB) をセットアップ、運用、スケーリングできます。 + 「[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 を行形式に変換します。 | タスク | 説明 | 必要なスキル | | --- | --- | --- | | Oracle データベースの JSON データを変換します。 | JSON データを行フォーマットに読み込むための Oracle SQL クエリを記述します。詳細と構文例については、「*追加情報*」セクションの「*Oracle\$1SQL\$1READ\$1JSON*」を参照してください。 | 移行エンジニア | | PostgreSQL データベース上の JSON データを変換します。 | JSON データを ROW フォーマットに読み込むための PostgreSQL クエリを記述します。詳細と構文例については、「*追加情報*」セクションの「*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/ja_jp/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/ja_jp/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 移行エンジニア | ### Oracle プロシージャを 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) ## 追加情報 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\$1テーブル\$1作成\$1挿入\$1スクリプト** ``` 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` -> account -> `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`が行の形式で返されます。 *前のクエリの結果* | | | 親口座番号 | 口座番号 | ビジネスユニット ID | ポジション ID | | --- |--- |--- |--- | | 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 ; ``` 大なり演算子(`>`)を1つ使うと、その`KEY`に定義された`VALUE`が返されます。例： `KEY`: `positionId`、そして `VALUE`: `"0100"` 1 つの大なり演算子 (`>`) を使用する場合、`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`を使用するには、二重大なり演算子を使用する必要があります。1 つの大なり演算子を使用すると、クエリは定義された`VALUE`を返します (例えば`KEY`：`positionId`、および`VALUE`：`"0100"`) を返します。二重大なり演算子 (`>>`) を使用すると、その`KEY`に定義されている実際の値(二重引用符なしの`KEY`：`positionId`、および`VALUE`：`0100`など) が返されます。 前のケースでは、`parentAccountNumber`は`INT`に`CAST`と入力し、`accountNumber`は`INT`に`CAST`と入力し、`businessUnitId`は`INT`に`CAST`と入力し、`positionId`は`VARCHAR`に`CAST`と入力します。 次の表は、1 つの大なり演算子 (`>`) と二重大なり演算子 (`>>`) の役割を説明するクエリ結果を示しています。 最初の表のクエリでは、1 つの大なり演算子 (`>`) を使用しています。各列は JSON 型で、別のデータ型に変換することはできません。 | | | 親アカウント番号 | AccountNumber | ビジネスユニット ID | ポジション ID | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | 0100 | | 2005284042 | 2005284042 | 6 | 0090 | | 2000272719 | 2000272719 | 1 | 0100 | 2 番目のテーブルでは、クエリは二重大なり演算子 (`>>`) を使用しています。各列は列の値に基づいて`CAST`型をサポートします。たとえば、この場合は `INTEGER` と指定します。 | | | 親アカウント番号 | AccountNumber | ビジネスユニット ID | ポジション ID | | --- |--- |--- |--- | | 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`の 2 つのパラメータを受け入れます。`KEY`パラメータは本質的にハードコーディングされているか、静的である必要があります。`VALUE`パラメータはテーブル出力から導出されます。 + `JSON_ARRAYAGG`は`JSON_OBJECT`をパラメーターとして受け入れます。これにより、`JSON_OBJECT`要素のセットをリストとしてグループ化できます。たとえば、複数のレコード (データセット内の複数の`KEY`と`VALUE`ペア) を持つ`JSON_OBJECT`要素がある場合、`JSON_ARRAYAGG`はデータセットを追加してリストを作成します。データ構造言語によると、`LIST`は要素のグループです。このコンテキストでは、`LIST`は`JSON_OBJECT`要素のグループです。 次の例は、1 つの`JSON_OBJECT`要素を示しています。 ``` { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } ``` 次の例には、2 つの`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`と`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. JSON クエリ付きの Oracle\$1Procedure\$1** このコードは 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; / ``` *プロシージャを実行する* 次のコードブロックでは、以前に作成した 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. JSON クエリ付きポストグレス関数** 関数の例 ``` 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 } } ] } ```