Übersicht Voraussetzungen und Einschränkungen Architektur Tools Bewährte Methoden Epen Zugehörige Ressourcen Zusätzliche Informationen

JSON-Oracle-Abfragen in PostgreSQL-Datenbank-SQL konvertieren

Pinesh Singal und Lokesh Gurram, Amazon Web Services

Übersicht

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_OBJECTJSON_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

Verwenden Sie AWS SCT mit dem JSON-Funktionscode, um den Quellcode von Oracle nach PostgreSQL zu konvertieren.
Die Konvertierung erzeugt PostgreSQL-unterstützte migrierte .sql-Dateien.
Konvertieren Sie die nicht konvertierten Oracle-JSON-Funktionscodes manuell in PostgreSQL-JSON-Funktionscodes.
Führen Sie die .sql-Dateien auf der Aurora PostgreSQL-kompatiblen Ziel-DB-Instance aus.

Tools

AWS-Services

Amazon Aurora 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 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) 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 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 ist ein Open-Source-Verwaltungstool für PostgreSQL. Es bietet eine grafische Oberfläche, mit der Sie Datenbankobjekte erstellen, verwalten und verwenden können. DBeaverist ein universelles Datenbanktool.

Bewährte Methoden

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_SQL_Read_JSON im Abschnitt Zusätzliche Informationen.

Epen

Aufgabe	Beschreibung	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_Table_Creation_Insert_Script, 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_Table_Creation_Insert_Script, das sich im Abschnitt Zusätzliche Informationen befindet.	Migrationsingenieur

Aufgabe	Beschreibung	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_SQL_Read_JSON* 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_SQL_Read_JSON* im Abschnitt Zusätzliche Informationen.*	Migrationsingenieur

Aufgabe	Beschreibung	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_SQL_JSON_Aggregation_Join im Abschnitt Zusätzliche Informationen. JOIN — Die Daten im JSON-Format werden als Eingabeparameter an die Abfrage übergeben. Zwischen diesen statischen Daten und den JSON-Daten in der Oracle-DB-Tabelle wird ein innerer JOIN erstellt. `aws_test_table` Aggregation mit Validierung — Die JSON-Daten haben `VALUE` Parameter mit Werten wie`accountNumber`, `businessUnitId` und `parentAccountNumberpositionId`, die für `SUM` `COUNT` Aggregationen verwendet werden. `KEY` JSON-Format — Nach dem Join und der Aggregation werden die Daten mithilfe von und im JSON-Format gemeldet. `JSON_OBJECT` `JSON_ARRAYAGG`	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_SQL_JSON_Aggregation_Join* im Abschnitt Zusätzliche Informationen.* JOIN — Die Daten im JSON-Format () werden als Eingabeparameter an die Klauselabfrage übergeben. `tab1` `WITH` Zwischen diesen statischen Daten und den JSON-Daten, die sich in der Tabelle befinden, wird ein JOIN erstellt. `tab` Ein JOIN wird auch mit der `WITH` Klausel erstellt, die JSON-Daten in der `aws_test_pg_table` Tabelle enthält. Aggregation — Die JSON-Daten haben `VALUE` Parameter mit Werten wie`accountNumber`,`parentAccountNumber`, und `businessUnitIdpositionId`, die für die `COUNT` Aggregationen `SUM` und verwendet werden. `KEY` JSON-Format — Nach dem Zusammenführen und der Aggregation werden die Daten mithilfe von und im JSON-Format gemeldet. `JSON_BUILD_OBJECT` `JSON_AGG`	Ingenieur für Migration

Aufgabe	Beschreibung	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_Procedure_with_JSON_Query* 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_Function_with_JSON_Query* im Abschnitt Zusätzliche Informationen befindet.*	Migrationsingenieur

Zugehörige Ressourcen

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_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

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 dataKEY. Das KEY accountNumber ist ein VALUE von accountKEY, und das account KEY ist ein VALUE von dataKEY.

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_TABLE erwartet Parameter im JSON-Format.

Jedes Element in COLUMNS hat ein vordefiniertesPATH, und dort wird ein VALUE für ein bestimmtes Element geeignetes Objekt im KEY Zeilenformat zurückgegeben.

Ergebnis der vorherigen Abfrage

PARENT_ACCOUNT_NUMBER	KONTONUMMER	ID DER GESCHÄFTSEINHEIT	POSITIONS-ID
32000	42000	7	0 100
32001	42001	6	0090

4 Postgres_SQL_Read_JSON

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 ;

PATHWird in Oracle verwendet, um das spezifische KEY und zu identifizierenVALUE. 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 erzwingenCAST, 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 BeispielKEY:positionId, undVALUE:. "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 verwendenCAST, 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:, undVALUE: positionId0100, ohne doppelte Anführungszeichen).

Im vorherigen Fall ist Typ zu, parentAccountNumber ist Typ CAST zuINT, accountNumber ist Typ CAST zuINT, 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_SQL_JSON_Aggregation_Join

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 wieJSON_OBJECT, JSON_ARRAYJSON_OBJECTAGG, und. JSON_ARRAYAGG

JSON_OBJECTakzeptiert zwei Parameter: KEY und. VALUE Der KEY Parameter sollte fest codiert oder statisch sein. Der VALUE Parameter wird aus der Tabellenausgabe abgeleitet.
JSON_ARRAYAGGakzeptiert 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_SQL_JSON_Aggregation_Join

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_Procedure_with_JSON_Query

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_Function_with_JSON_Query

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
      }
    }
  ]
}

Warnung JavaScript ist in Ihrem Browser nicht verfügbar oder deaktiviert.

Zur Nutzung der AWS-Dokumentation muss JavaScript aktiviert sein. Weitere Informationen finden auf den Hilfe-Seiten Ihres Browsers.

Dokumentkonventionen

Stellen Sie eine Connect über einen SSH-Tunnel in pgAdmin her

Amazon DynamoDB-Tabellen zwischen Konten kopieren