Riepilogo Prerequisiti e limitazioni Architettura Strumenti Best practice Epiche Risorse correlate Informazioni aggiuntive

Convertire le query JSON Oracle in SQL del database PostgreSQL

Pinesh Singal e Lokesh Gurram, Amazon Web Services

Riepilogo

Questo processo di migrazione per il passaggio dall'ambiente locale al cloud Amazon Web Services (AWS) utilizza AWS Schema Conversion Tool (AWS SCT) per convertire il codice da un database Oracle in un database PostgreSQL. La maggior parte del codice viene convertita automaticamente da AWS SCT. Tuttavia, le query Oracle relative a JSON non vengono convertite automaticamente.

A partire dalla versione Oracle 12.2, Oracle Database supporta varie funzioni JSON che aiutano a convertire i dati basati su JSON in dati basati su Row. Tuttavia, AWS SCT non converte automaticamente i dati basati su JSON in un linguaggio supportato da PostgreSQL.

Questo modello di migrazione si concentra principalmente sulla conversione manuale delle query Oracle relative a JSON con funzioni come JSON_OBJECT e JSON_TABLE da un database Oracle a un database PostgreSQL. JSON_ARRAYAGG

Prerequisiti e limitazioni

Prerequisiti

Un account AWS attivo
Un'istanza di database Oracle locale (attiva e funzionante)
Un'istanza di database Amazon Relational Database Service (Amazon RDS) per PostgreSQL o Amazon Aurora PostgreSQL Compatible Edition (attiva e funzionante)

Limitazioni

Le query KEY relative a JSON richiedono un formato e fisso. VALUE Il mancato utilizzo di quel formato restituisce un risultato errato.
Se una modifica nella struttura JSON aggiunge nuove KEY VALUE coppie nella sezione dei risultati, è necessario modificare la procedura o la funzione corrispondente nella query SQL.
Alcune funzioni relative a JSON sono supportate nelle versioni precedenti di Oracle e PostgreSQL ma con meno funzionalità.

Versioni del prodotto

Oracle Database versione 12.2 e successive
Amazon RDS for PostgreSQL o Aurora PostgreSQL versione 9.5 e successive
Versione più recente di AWS SCT (testata utilizzando la versione 1.0.664)

Architettura

Stack tecnologico di origine

Un'istanza di database Oracle con versione 19c

Stack tecnologico Target

Un'istanza di database compatibile con Amazon RDS for PostgreSQL o Aurora PostgreSQL con versione 13

Architettura Target

Usa AWS SCT con il codice della funzione JSON per convertire il codice sorgente da Oracle a PostgreSQL.
La conversione produce file.sql migrati supportati da PostgreSQL.
Converti manualmente i codici funzione Oracle JSON non convertiti in codici funzione JSON PostgreSQL.
Esegui i file.sql sull'istanza DB di destinazione compatibile con Aurora PostgreSQL.

Strumenti

Servizi AWS

Amazon Aurora è un motore di database relazionale completamente gestito creato per il cloud e compatibile con MySQL e PostgreSQL.
Amazon Relational Database Service (Amazon RDS) per PostgreSQL ti aiuta a configurare, gestire e scalare un database relazionale PostgreSQL nel cloud AWS.
AWS Schema Conversion Tool (AWS SCT) supporta migrazioni di database eterogenei convertendo automaticamente lo schema del database di origine e la maggior parte del codice personalizzato in un formato compatibile con il database di destinazione.

Altri servizi

Oracle SQL Developer è un ambiente di sviluppo integrato che semplifica lo sviluppo e la gestione dei database Oracle nelle implementazioni tradizionali e basate sul cloud.
pGadmin o DBeaver. pgAdmin è uno strumento di gestione open source per PostgreSQL. Fornisce un'interfaccia grafica che consente di creare, gestire e utilizzare oggetti di database. DBeaverè uno strumento di database universale.

Best practice

La query di Oracle ha il tipo CAST come impostazione predefinita quando si utilizza la JSON_TABLE funzione. Una best practice consiste CAST nell'utilizzarla anche in PostgreSQL, utilizzando il doppio dei caratteri maggiori di (). >>

Per ulteriori informazioni, consulta Postgres_SQL_read_JSON nella sezione Informazioni aggiuntive.

Epiche

Attività	Descrizione	Competenze richieste
Memorizza i dati JSON nel database Oracle.	Crea una tabella nel database Oracle e archivia i dati JSON nella `CLOB` colonna. Utilizzate Oracle_Table_Creation_Insert_Script che si trova nella sezione* Informazioni aggiuntive.*	Ingegnere della migrazione
Archivia i dati JSON nel database PostgreSQL.	Crea una tabella nel database PostgreSQL e archivia i dati JSON nella colonna. `TEXT` Usa Postgres_Table_Creation_Insert_Script* che si trova nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione

Attività	Descrizione	Competenze richieste
Convertire i dati JSON sul database Oracle.	Scrivi una query Oracle SQL per leggere i dati JSON in formato ROW. Per ulteriori dettagli ed esempi di sintassi, vedere Oracle_SQL_read_JSON* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione
Converti i dati JSON nel database PostgreSQL.	Scrivi una query PostgreSQL per leggere i dati JSON in formato ROW. Per maggiori dettagli ed esempi di sintassi, consulta Postgres_SQL_read_JSON* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione

Attività	Descrizione	Competenze richieste
Esegui aggregazioni e convalide sulla query Oracle SQL.	Per convertire manualmente i dati JSON, esegui un'unione, un'aggregazione e una convalida sulla query Oracle SQL e riporta l'output in formato JSON. Utilizza il codice in Oracle_SQL_JSON_AGGREGATION_join nella sezione Informazioni aggiuntive. JOIN — I dati in formato JSON vengono passati come parametro di input alla query. Viene creato un JOIN interno tra questi dati statici e i dati JSON nella tabella Oracle DB. `aws_test_table` Aggregazione con convalida: i dati JSON hanno `VALUE` parametri con valori come`accountNumber`, `KEY` `businessUnitId` e `parentAccountNumberpositionId`, che vengono utilizzati per le aggregazioni. `SUM` `COUNT` Formato JSON: dopo l'unione e l'aggregazione, i dati vengono riportati in formato JSON utilizzando e. `JSON_OBJECT` `JSON_ARRAYAGG`	Ingegnere della migrazione
Esegui aggregazioni e convalide sulla query SQL di Postgres.	Per convertire manualmente i dati JSON, esegui un'unione, un'aggregazione e una convalida sulla query PostgreSQL e riporta l'output in formato JSON. Usa il codice in Postgres_SQL_JSON_Aggregation_join nella sezione Informazioni aggiuntive. JOIN — I dati in formato JSON () vengono passati come parametro di input alla query della clausola. `tab1` `WITH` Viene creato un JOIN tra questi dati statici e i dati JSON, che si trovano nella tabella. `tab` Viene inoltre creato un JOIN con la `WITH` clausola, che contiene dati JSON nella tabella. `aws_test_pg_table` Aggregazione: i dati JSON hanno `KEY` `VALUE` parametri con valori come`accountNumber`,, e `parentAccountNumber` `businessUnitIdpositionId`, che vengono utilizzati per le aggregazioni e. `SUM` `COUNT` Formato JSON: dopo l'unione e l'aggregazione, i dati vengono riportati in formato JSON utilizzando e. `JSON_BUILD_OBJECT` `JSON_AGG`	Ingegnere della migrazione

Attività	Descrizione	Competenze richieste
Convertire le query JSON nella procedura Oracle in righe.	Per la procedura Oracle di esempio, utilizzate la precedente query Oracle e il codice in ORACLE_PROCEDURE_with_JSON_Query* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione
Converti le funzioni PostgreSQL che contengono query JSON in dati basati su righe.	Per le funzioni PostgreSQL di esempio, usa la precedente query PostgreSQL e il codice che si trova in Postgres_function_with_JSON_Query nella sezione Informazioni aggiuntive.	Ingegnere della migrazione

Risorse correlate

Informazioni aggiuntive

Per convertire il codice JSON dal database Oracle al database PostgreSQL, utilizzare i seguenti script, nell'ordine.

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

I seguenti blocchi di codice mostrano come convertire i dati Oracle JSON in formato riga.

Query e sintassi di esempio


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 );

Il documento JSON memorizza i dati come raccolte. Ogni raccolta può avere KEY e VALUE accoppiare. Ognuno VALUE può avere nidi KEY e VALUE coppie. La tabella seguente fornisce informazioni sulla lettura delle specifiche VALUE del documento JSON.

KEY	HIERARCHY o PATH da utilizzare per ottenere il VALORE	VALUE
`profileType`	`metadata` -> `profileType`	«P»
`positionId`	`data` -> `positionId`	«100"
`accountNumber`	`data`-> conto -> `accountNumber`	42000

Nella tabella precedente, KEY profileType è un VALUE dei metadataKEY. Il KEY positionId è uno VALUE dei dataKEY. Il KEY accountNumber è un VALUE dei accountKEY, e il account KEY è un VALUE dei dataKEY.

Esempio di documento 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"
      }
    ]
  }
}

Query SQL utilizzata per ottenere i campi selezionati dal documento JSON


select parent_account_number,account_number,business_unit_id,position_id from aws_test_table aws,JSON_TABLE ( json_doc, '$' ERROR ON ERROR
COLUMNS (
parent_account_number NUMBER PATH '$.data.account.parentAccountNumber',
account_number NUMBER PATH '$.data.account.accountNumber',
business_unit_id NUMBER PATH '$.data.account.businessUnitId',
position_id VARCHAR2 ( 4 ) PATH '$.data.positionId'
)) as sc

Nella query precedente, JSON_TABLE è una funzione integrata in Oracle che converte i dati JSON in formato riga. La funzione JSON_TABLE prevede parametri in formato JSON.

Ogni elemento COLUMNS ha un valore predefinito PATH e lì viene restituito un valore appropriato VALUE per un dato KEY elemento in formato riga.

Risultato della query precedente

PARENT_ACCOUNT_NUMBER	NUMERO_CONTO	ID_UNITÀ_AZIENDALE	ID_POSIZIONE
32000	42000	7	0100
32001	42001	6	0090

4. Postgres_sql_read_json

Query e sintassi di esempio


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 ;

In Oracle, PATH viene utilizzato per identificare lo specifico KEY eVALUE. Tuttavia, PostgreSQL utilizza HIERARCHY un modello per la lettura e da JSON. KEY VALUE Gli stessi dati JSON menzionati di seguito vengono utilizzati negli Oracle_SQL_Read_JSON esempi seguenti.

La query SQL di tipo CAST non è consentita

(Se si forza il tipoCAST, la query ha esito negativo e viene generato un errore di sintassi).


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 ;

L'utilizzo di un singolo operatore maggiore di (>) restituirà il VALUE relativo valore definito. KEY Ad esempio,KEY: e:positionId. VALUE "0100"

CASTIl tipo non è consentito quando si utilizza il singolo operatore maggiore di (). >

È consentita una query SQL di tipo CAST


select *
from ( 
select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber, 
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber, 
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId, 
(json_doc::json->'data'->>'positionId')::varchar as positionId 
from aws_test_pg_table) d ;

Per utilizzare typeCAST, è necessario utilizzare l'operatore double greater-than. Se si utilizza il singolo operatore maggiore di, la query restituisce il valore VALUE definito (ad esempio,KEY: e:). positionId VALUE "0100" L'utilizzo dell'operatore double greater-than (>>) restituirà il valore effettivo definito per tale operazione KEY (ad esempio,KEY: eVALUE: positionId0100, senza virgolette doppie).

Nel caso precedente, is type to, parentAccountNumber is type CAST toINT, accountNumber is type CAST to INT e businessUnitId is type CAST INT to. positionId CAST VARCHAR

Le tabelle seguenti mostrano i risultati delle interrogazioni che spiegano il ruolo del singolo operatore maggiore di (>) e del doppio operatore maggiore di (). >>

Nella prima tabella, la query utilizza il singolo operatore maggiore di (). > Ogni colonna è di tipo JSON e non può essere convertita in un altro tipo di dati.

parentAccountNumber	Numero di conto	businessUnitId	ID di posizione
2003565430	2003564830	7	«0100"
2005284042	2005284042	6	«0090"
2000272719	2000272719	1	«0100"

Nella seconda tabella, la query utilizza l'operatore double greater-than (). >> Ogni colonna supporta il tipo in CAST base al valore della colonna. Ad esempio, INTEGER in questo contesto.

parentAccountNumber	Numero di conto	businessUnitId	ID di posizione
2003565430	2003564830	7	0100
2005284042	2005284042	6	0090
2000272719	2000272719	1	0100

5. Oracle_SQL_JSON_AGGREGATION_JOIN

Query di esempio


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 
    );

Per convertire i dati a livello di riga in formato JSON, Oracle dispone di funzioni integrate comeJSON_OBJECT,JSON_ARRAY, JSON_OBJECTAGG e. JSON_ARRAYAGG

JSON_OBJECTaccetta due parametri: e. KEY VALUE Il KEY parametro deve essere codificato o di natura statica. Il VALUE parametro è derivato dall'output della tabella.
JSON_ARRAYAGGaccetta JSON_OBJECT come parametro. Questo aiuta a raggruppare l'insieme di JSON_OBJECT elementi in un elenco. Ad esempio, se hai un JSON_OBJECT elemento con più record (multipli KEY e VALUE coppie nel set di dati), JSON_ARRAYAGG aggiunge il set di dati e crea un elenco. Secondo il linguaggio Data Structure, LIST è un gruppo di elementi. In questo contesto, LIST è un gruppo di JSON_OBJECT elementi.

L'esempio seguente mostra un JSON_OBJECT elemento.


{
   "taxProfessionalCount": 0,
   "attorneyCount": 0,
   "nonAttorneyCount": 1,
   "clerkCount": 0
}

Il prossimo esempio mostra due JSON_OBJECT elementi, LIST indicati da parentesi quadre ([ ]).


[ 
    {
        "taxProfessionalCount": 0,
        "attorneyCount": 0,
        "nonAttorneyCount": 1,
        "clerkCount": 0
      }
,
    {
        "taxProfessionalCount": 2,
        "attorneyCount": 1,
        "nonAttorneyCount": 3,
        "clerkCount":4
      }
]

Esempio di query 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 
    );

Esempio di output della precedente query 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_SQL_JSON_AGGREGATION_JOIN

Le JSON_BUILD_OBJECT funzioni JSON_AGG integrate di PostgreSQL convertono i dati a livello di riga in formato JSON. JSON_BUILD_OBJECTPostgreSQL e sono equivalenti a Oracle JSON_AGG e. JSON_OBJECT JSON_ARRAYAGG

Query di esempio


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;

Esempio di output della query precedente

L'output di Oracle e PostgreSQL è esattamente lo stesso.


{
  "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

Questo codice converte la procedura Oracle in una funzione PostgreSQL con query SQL JSON. Mostra come la query traspone JSON in righe e viceversa.


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;
/

Esecuzione della procedura

Il seguente blocco di codice spiega come eseguire la procedura Oracle creata in precedenza con un esempio di input JSON per la procedura. Fornisce inoltre il risultato o l'output di questa procedura.


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;
/

Output della procedura


{
  "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

Funzione di esempio


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;  
$$;

Esecuzione della funzione


select    f_pg_json_test('{ 
        "accounts": [{ 
          "accountNumber": 42001, 
          "parentAccountNumber": 32001, 
          "businessUnitId": 6 
        }, { 
          "accountNumber": 42000, 
          "parentAccountNumber": 32000, 
          "businessUnitId": 7 
        }] 
      }')   ;

Uscita della funzione

L'output seguente è simile all'output della procedura Oracle. La differenza è che questo output è in formato testo.


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

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Connect utilizzando un tunnel SSH in pGAdmin

Copia le tabelle Amazon DynamoDB tra più account