Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich. # JSON-Oracle-Abfragen in PostgreSQL-Datenbank-SQL konvertieren *Pinesh Singal und Lokesh Gurram, Amazon Web Services* ## Zusammenfassung Dieser Migrationsprozess für den Umstieg von der lokalen Umgebung zur Amazon Web Services (AWS) -Cloud verwendet das AWS Schema Conversion Tool (AWS SCT), um den Code aus einer Oracle-Datenbank in eine PostgreSQL-Datenbank zu konvertieren. Der größte Teil des Codes wird automatisch von AWS SCT konvertiert. JSON-bezogene Oracle-Abfragen werden jedoch nicht automatisch konvertiert. Ab der Version Oracle 12.2 unterstützt Oracle Database verschiedene JSON-Funktionen, die bei der Konvertierung von JSON-basierten Daten in zeilenbasierte Daten helfen. AWS SCT konvertiert JSON-basierte Daten jedoch nicht automatisch in eine Sprache, die von PostgreSQL unterstützt wird. Dieses Migrationsmuster konzentriert sich hauptsächlich auf die manuelle Konvertierung der JSON-bezogenen Oracle-Abfragen mit Funktionen wie `JSON_OBJECT``JSON_ARRAYAGG`, und `JSON_TABLE` von einer Oracle-Datenbank in eine PostgreSQL-Datenbank. ## Voraussetzungen und Einschränkungen **Voraussetzungen** + Ein aktives AWS-Konto + Eine lokale Oracle-Datenbankinstanz (läuft) + Eine Amazon Relational Database Service (Amazon RDS) für PostgreSQL oder Amazon Aurora PostgreSQL-kompatible Edition-Datenbank-Instance (betriebsbereit) **Einschränkungen** + JSON-bezogene Abfragen erfordern ein festes UND-Format. `KEY` `VALUE` Wenn dieses Format nicht verwendet wird, wird das falsche Ergebnis zurückgegeben. + Wenn durch eine Änderung der JSON-Struktur neue `KEY` und `VALUE` Paare im Ergebnisabschnitt hinzugefügt werden, muss die entsprechende Prozedur oder Funktion in der SQL-Abfrage geändert werden. + Einige JSON-bezogene Funktionen werden in früheren Versionen von Oracle und PostgreSQL unterstützt, verfügen jedoch über weniger Funktionen. **Produktversionen** + Oracle Database Version 12.2 und höher + Amazon RDS for PostgreSQL oder Aurora PostgreSQL-kompatible Version 9.5 und höher + Aktuelle Version von AWS SCT (getestet mit Version 1.0.664) ## Architektur **Quelltechnologie-Stack** + Eine Oracle-Datenbankinstanz mit Version 19c **Zieltechnologie-Stack** + Eine Amazon RDS for PostgreSQL- oder Aurora PostgreSQL-kompatible Datenbank-Instance mit Version 13 **Zielarchitektur** ![\[Die Beschreibung folgt dem Diagramm.\]](http://docs.aws.amazon.com/de_de/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png) 1. Verwenden Sie AWS SCT mit dem JSON-Funktionscode, um den Quellcode von Oracle nach PostgreSQL zu konvertieren. 1. Die Konvertierung erzeugt PostgreSQL-unterstützte migrierte .sql-Dateien. 1. Konvertieren Sie die nicht konvertierten Oracle-JSON-Funktionscodes manuell in PostgreSQL-JSON-Funktionscodes. 1. Führen Sie die .sql-Dateien auf der Aurora PostgreSQL-kompatiblen Ziel-DB-Instance aus. ## Tools **AWS-Services** + [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) ist eine vollständig verwaltete relationale Datenbank-Engine, die für die Cloud entwickelt wurde und mit MySQL und PostgreSQL kompatibel ist. + [Amazon Relational Database Service (Amazon RDS) für PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) unterstützt Sie bei der Einrichtung, dem Betrieb und der Skalierung einer relationalen PostgreSQL-Datenbank in der AWS-Cloud. + Das [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) unterstützt heterogene Datenbankmigrationen, indem das Quelldatenbankschema und ein Großteil des benutzerdefinierten Codes automatisch in ein Format konvertiert werden, das mit der Zieldatenbank kompatibel ist. **Andere Dienste** + [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) ist eine integrierte Entwicklungsumgebung, die die Entwicklung und Verwaltung von Oracle-Datenbanken sowohl in herkömmlichen als auch in Cloud-basierten Bereitstellungen vereinfacht. + pgAdmin oder DBeaver. [pgAdmin](https://www.pgadmin.org/) ist ein Open-Source-Verwaltungstool für PostgreSQL. Es bietet eine grafische Oberfläche, mit der Sie Datenbankobjekte erstellen, verwalten und verwenden können. [DBeaver](https://dbeaver.io/)ist ein universelles Datenbanktool. ## Best Practices Die Oracle-Abfrage verwendet `CAST` standardmäßig den Typ, wenn die `JSON_TABLE` Funktion verwendet wird. Eine bewährte Methode ist die Verwendung auch `CAST` in PostgreSQL, wobei das Doppelte größer als Zeichen () verwendet wird. `>>` *Weitere Informationen finden Sie unter *Postgres\$1SQL\$1Read\$1JSON* im Abschnitt Zusätzliche Informationen.* ## Epen ### Generieren Sie die JSON-Daten in den Oracle- und PostgreSQL-Datenbanken | Aufgabe | Description | Erforderliche Fähigkeiten | | --- | --- | --- | | Speichern Sie die JSON-Daten in der Oracle-Datenbank. | Erstellen Sie eine Tabelle in der Oracle-Datenbank und speichern Sie die JSON-Daten in der `CLOB` Spalte.  *Verwenden Sie das *Oracle\$1Table\$1Creation\$1Insert\$1Script*, das sich im Abschnitt Zusätzliche Informationen befindet.* | Migrationsingenieur | | Speichern Sie die JSON-Daten in der PostgreSQL-Datenbank. | Erstellen Sie eine Tabelle in der PostgreSQL-Datenbank und speichern Sie die JSON-Daten in der `TEXT` Spalte. *Verwenden Sie das *Postgres\$1Table\$1Creation\$1Insert\$1Script*, das sich im Abschnitt Zusätzliche Informationen befindet.* | Migrationsingenieur | ### Konvertiert das JSON in das ROW-Format | Aufgabe | Description | Erforderliche Fähigkeiten | | --- | --- | --- | | Konvertieren Sie die JSON-Daten in der Oracle-Datenbank. | Schreiben Sie eine Oracle-SQL-Abfrage, um die JSON-Daten in das ROW-Format zu lesen. *Weitere Informationen und eine Beispielsyntax finden Sie unter *Oracle\$1SQL\$1Read\$1JSON* im Abschnitt Zusätzliche Informationen.* | Migrationsingenieur | | Konvertiert die JSON-Daten in der PostgreSQL-Datenbank. | Schreiben Sie eine PostgreSQL-Abfrage, um die JSON-Daten in das ROW-Format zu lesen. *Weitere Informationen und eine Beispielsyntax finden Sie unter *Postgres\$1SQL\$1Read\$1JSON* im Abschnitt Zusätzliche Informationen.* | Migrationsingenieur | ### Konvertieren Sie die JSON-Daten manuell mithilfe der SQL-Abfrage und melden Sie die Ausgabe im JSON-Format | Aufgabe | Description | Erforderliche Fähigkeiten | | --- | --- | --- | | Führen Sie Aggregationen und Validierungen für die Oracle SQL-Abfrage durch. | Um die JSON-Daten manuell zu konvertieren, führen Sie eine Verknüpfung, Aggregation und Validierung der Oracle SQL-Abfrage durch und melden Sie die Ausgabe im JSON-Format. *Verwenden Sie den Code unter *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join im Abschnitt Zusätzliche Informationen*.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/de_de/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingenieur für Migration | | Führen Sie Aggregationen und Validierungen für die Postgres-SQL-Abfrage durch. | Um die JSON-Daten manuell zu konvertieren, führen Sie eine Verknüpfung, Aggregation und Validierung der PostgreSQL-Abfrage durch und melden Sie die Ausgabe im JSON-Format. *Verwenden Sie den Code unter *Postgres\$1SQL\$1JSON\$1Aggregation\$1Join* im Abschnitt Zusätzliche Informationen.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/de_de/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingenieur für Migration | ### Konvertieren Sie die Oracle-Prozedur in eine PostgreSQL-Funktion, die JSON-Abfragen enthält | Aufgabe | Description | Erforderliche Fähigkeiten | | --- | --- | --- | | Konvertieren Sie die JSON-Abfragen in der Oracle-Prozedur in Zeilen. | *Verwenden Sie für die Oracle-Beispielprozedur die vorherige Oracle-Abfrage und den Code unter *Oracle\$1Procedure\$1with\$1JSON\$1Query* im Abschnitt Zusätzliche Informationen.* | Ingenieur für Migration | | Konvertieren Sie die PostgreSQL-Funktionen mit JSON-Abfragen in zeilenbasierte Daten. | *Verwenden Sie für die PostgreSQL-Beispielfunktionen die vorherige PostgreSQL-Abfrage und den Code, der sich unter *Postgres\$1Function\$1with\$1JSON\$1Query* im Abschnitt Zusätzliche Informationen befindet.* | Migrationsingenieur | ## Zugehörige Ressourcen + [Oracle JSON-Funktionen](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html) + [PostgreSQL JSON-Funktionen](https://www.postgresql.org/docs/13/functions-json.html) + [Beispiele für Oracle JSON-Funktionen](https://oracle-base.com/articles/12c/sql-json-functions-12cr2) + [Beispiele für PostgreSQL JSON-Funktionen](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg) + [AWS-Schemakonvertierungstool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) ## Zusätzliche Informationen Verwenden Sie die folgenden Skripten der Reihe nach, um JSON-Code von der Oracle-Datenbank in die PostgreSQL-Datenbank zu konvertieren. **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** Die folgenden Codeblöcke zeigen, wie Oracle JSON-Daten in das Zeilenformat konvertiert werden. *Beispielabfrage und Syntax* ``` 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 ); ``` Das JSON-Dokument speichert die Daten als Sammlungen. Jede Sammlung kann aus `KEY` und aus `VALUE` Paaren bestehen. Jeder `VALUE` kann Verschachtelungen `KEY` und `VALUE` Paare haben. Die folgende Tabelle enthält Informationen zum Lesen der spezifischen Daten `VALUE` aus dem JSON-Dokument. | | | KEY | HIERARCHY oder PATH, die zum Abrufen des WERTS verwendet werden sollen | VALUE | | --- |--- |--- | | `profileType` | `metadata` -> `profileType` | „P“ | | `positionId` | `data` -> `positionId` | „0100" | | `accountNumber` | `data`-> Konto -> `accountNumber` | 42000 | In der vorherigen Tabelle `KEY` `profileType` ist der ein `VALUE` von. `metadata` `KEY` Das `KEY` `positionId` ist ein `VALUE` von `data``KEY`. Das `KEY` `accountNumber` ist ein `VALUE` von `account``KEY`, und das `account` `KEY` ist ein `VALUE` von `data``KEY`. *Beispiel für ein JSON-Dokument* ``` { "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-Abfrage, die verwendet wird, um die ausgewählten Felder aus dem JSON-Dokument abzurufen* ``` 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 ``` In der vorherigen Abfrage `JSON_TABLE` ist eine in Oracle integrierte Funktion enthalten, die die JSON-Daten in das Zeilenformat konvertiert. Die Funktion JSON\$1TABLE erwartet Parameter im JSON-Format. Jedes Element in `COLUMNS` hat ein vordefiniertes`PATH`, und dort wird ein `VALUE` für ein bestimmtes Element geeignetes Objekt im `KEY` Zeilenformat zurückgegeben. *Ergebnis der vorherigen Abfrage* | | | PARENT\$1ACCOUNT\$1NUMBER | KONTONUMMER | ID DER GESCHÄFTSEINHEIT | POSITIONS-ID | | --- |--- |--- |--- | | 32000 | 42000 | 7 | 0 100 | | 32001 | 42001 | 6 | 0090 | **4 Postgres\$1SQL\$1Read\$1JSON** *****Beispielabfrage und Syntax* ``` 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 ; ``` `PATH`Wird in Oracle verwendet, um das spezifische `KEY` und zu identifizieren`VALUE`. PostgreSQL verwendet jedoch ein `HIERARCHY` Modell zum Lesen `KEY` und `VALUE` aus JSON. In den folgenden Beispielen werden dieselben JSON-Daten `Oracle_SQL_Read_JSON` verwendet, die unten erwähnt werden. *SQL-Abfrage mit dem Typ CAST ist nicht zulässig* (Wenn Sie die Eingabe erzwingen`CAST`, schlägt die Abfrage mit einem Syntaxfehler fehl.) ``` 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 ; ``` Wenn Sie einen einzigen Operator für größer als (`>`) verwenden, wird der dafür `VALUE` definierte Wert zurückgegeben. `KEY` Zum Beispiel`KEY`:`positionId`, und`VALUE`:. `"0100"` Typ `CAST` ist nicht zulässig, wenn Sie den einzelnen Operator „Größer als“ () `>` verwenden. *SQL-Abfrage mit dem Typ CAST ist zulässig* ``` 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 ; ``` Um den Typ zu verwenden`CAST`, müssen Sie den Operator Double Greater-than verwenden. Wenn Sie den Operator „Größer als“ verwenden, gibt die Abfrage den `VALUE` definierten Operator zurück (z. B.`KEY`:`positionId`, und:). `VALUE` `"0100"` Wenn Sie den Operator für ein doppeltes Größer-als-Zeichen (`>>`) verwenden, wird der tatsächlich dafür definierte Wert zurückgegeben `KEY` (z. B.`KEY`:, und`VALUE`: `positionId``0100`, ohne doppelte Anführungszeichen). Im vorherigen Fall ist Typ zu, `parentAccountNumber` ist Typ `CAST` zu`INT`, `accountNumber` ist Typ `CAST` zu`INT`, `businessUnitId` ist Typ `CAST` zu `INT` und `positionId` ist Typ `CAST` zu. `VARCHAR` Die folgenden Tabellen zeigen Abfrageergebnisse, in denen die Rolle des Operators für einen einzelnen Größer-als-Operator (`>`) und des doppelten Größer-als-Operators () erklärt wird. `>>` In der ersten Tabelle verwendet die Abfrage den einzigen Operator „Größer als“ (). `>` Jede Spalte ist vom Typ JSON und kann nicht in einen anderen Datentyp konvertiert werden. | | | parentAccountNumber | Kontonummer | businessUnitId | Positions-ID | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | „0100" | | 2005284042 | 2005284042 | 6 | „0090" | | 2000272719 | 2000272719 | 1 | „0100" | In der zweiten Tabelle verwendet die Abfrage den doppelten Größer-als-Operator (). `>>` Jede Spalte unterstützt den Typ, der auf dem Spaltenwert `CAST` basiert. Zum Beispiel `INTEGER` in diesem Kontext. | | | parentAccountNumber | Kontonummer | businessUnitId | Positions-ID | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | 0 100 | | 2005284042 | 2005284042 | 6 | 0090 | | 2000272719 | 2000272719 | 1 | 0 100 | **5. Oracle\$1SQL\$1JSON\$1Aggregation\$1Join** *Beispiel für eine Abfrage* ``` 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 ); ``` Um die Daten auf Zeilenebene in das JSON-Format zu konvertieren, verfügt Oracle über integrierte Funktionen wie`JSON_OBJECT`, `JSON_ARRAY``JSON_OBJECTAGG`, und. `JSON_ARRAYAGG` + `JSON_OBJECT`akzeptiert zwei Parameter: `KEY` und. `VALUE` Der `KEY` Parameter sollte fest codiert oder statisch sein. Der `VALUE` Parameter wird aus der Tabellenausgabe abgeleitet. + `JSON_ARRAYAGG`akzeptiert `JSON_OBJECT` als Parameter. Dies hilft bei der Gruppierung des Satzes von `JSON_OBJECT` Elementen als Liste. Wenn Sie beispielsweise ein `JSON_OBJECT` Element haben, das mehrere Datensätze hat (mehrere `KEY` und `VALUE` Paare im Datensatz), `JSON_ARRAYAGG` fügen Sie den Datensatz an und erstellen eine Liste. Gemäß der Datenstruktursprache `LIST` handelt es sich um eine Gruppe von Elementen. In diesem Zusammenhang `LIST` handelt es sich um eine Gruppe von `JSON_OBJECT` Elementen. Das folgende Beispiel zeigt ein `JSON_OBJECT` Element. ``` { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } ``` Das nächste Beispiel zeigt zwei `JSON_OBJECT` Elemente, die durch eckige Klammern (`[ ]`) `LIST` gekennzeichnet sind. ``` [ { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } , { "taxProfessionalCount": 2, "attorneyCount": 1, "nonAttorneyCount": 3, "clerkCount":4 } ] ``` *Beispiel für eine SQL-Abfrage* ``` 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 ); ``` *Beispielausgabe der vorherigen SQL-Abfrage* ``` { "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** Die integrierten Funktionen `JSON_BUILD_OBJECT` von PostgreSQL `JSON_AGG` konvertieren die Daten auf Zeilenebene in das JSON-Format.  PostgreSQL `JSON_BUILD_OBJECT` und `JSON_AGG` entsprechen Oracle `JSON_OBJECT` und. `JSON_ARRAYAGG` *Beispielabfrage* ``` 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; ``` *Beispielausgabe aus der vorherigen Abfrage* Die Ausgabe von Oracle und PostgreSQL ist exakt identisch. ``` { "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** Dieser Code konvertiert die Oracle-Prozedur in eine PostgreSQL-Funktion mit JSON-SQL-Abfragen. Es zeigt, wie die Abfrage JSON in Zeilen und umgekehrt transponiert. ``` 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; / ``` *Das Verfahren wird ausgeführt* Der folgende Codeblock erklärt, wie Sie die zuvor erstellte Oracle-Prozedur mit einer Beispiel-JSON-Eingabe für die Prozedur ausführen können. Außerdem erhalten Sie das Ergebnis oder die Ausgabe dieser Prozedur. ``` 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; / ``` *Ausgabe der Prozedur* ``` { "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** *Beispielfunktion* ``` 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; $$; ``` *Die Funktion wird ausgeführt* ``` select f_pg_json_test('{ "accounts": [{ "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }, { "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }] }') ; ``` *Ausgabe der Funktion* Die folgende Ausgabe ähnelt der Ausgabe der Oracle-Prozedur. Der Unterschied besteht darin, dass diese Ausgabe im Textformat vorliegt. ``` { "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 } } ] } ```