Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.
# 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)
## Architecture
**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**
![\[La descrizione segue il diagramma.\]](http://docs.aws.amazon.com/it_it/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png)
1. Usa AWS SCT con il codice della funzione JSON per convertire il codice sorgente da Oracle a PostgreSQL.
1. La conversione produce file.sql migrati supportati da PostgreSQL.
1. Converti manualmente i codici funzione Oracle JSON non convertiti in codici funzione JSON PostgreSQL.
1. Esegui i file.sql sull'istanza DB di destinazione compatibile con Aurora PostgreSQL.
## Tools (Strumenti)
**Servizi AWS**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) è 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) ti aiuta a configurare, gestire e scalare un database relazionale PostgreSQL nel cloud AWS.
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) 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](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) è 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](https://www.pgadmin.org/) è uno strumento di gestione open source per PostgreSQL. Fornisce un'interfaccia grafica che consente di creare, gestire e utilizzare oggetti di database. [DBeaver](https://dbeaver.io/)è 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\$1SQL\$1read\$1JSON* nella sezione Informazioni aggiuntive.*
## Epiche
### Genera i dati JSON nei database Oracle e PostgreSQL
| Operazione | Description | 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\$1Table\$1Creation\$1Insert\$1Script 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\$1Table\$1Creation\$1Insert\$1Script* che si trova nella sezione Informazioni aggiuntive.* | Ingegnere della migrazione |
### Converti il JSON in formato ROW
| Operazione | Description | 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\$1SQL\$1read\$1JSON* 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\$1SQL\$1read\$1JSON* nella sezione Informazioni aggiuntive.* | Ingegnere della migrazione |
### Converti manualmente i dati JSON utilizzando la query SQL e riporta l'output in formato JSON
| Operazione | Description | 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\$1SQL\$1JSON\$1AGGREGATION\$1join nella sezione Informazioni aggiuntive*.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | 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\$1SQL\$1JSON\$1Aggregation\$1join nella sezione Informazioni aggiuntive.**[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/it_it/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingegnere della migrazione |
### Convertire la procedura Oracle in una funzione PostgreSQL che contiene query JSON
| Operazione | Description | 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\$1PROCEDURE\$1with\$1JSON\$1Query* 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\$1function\$1with\$1JSON\$1Query nella sezione Informazioni aggiuntive.** | Ingegnere della migrazione |
## Risorse correlate
+ [Funzioni Oracle JSON](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html)
+ [Funzioni PostgreSQL JSON](https://www.postgresql.org/docs/13/functions-json.html)
+ [Esempi di funzioni JSON di Oracle](https://oracle-base.com/articles/12c/sql-json-functions-12cr2)
+ [Esempi di funzioni JSON PostgreSQL](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg)
+ [Strumento di conversione dello schema AWS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
## Informazioni aggiuntive
Per convertire il codice JSON dal database Oracle al database PostgreSQL, utilizzare i seguenti script, nell'ordine.
**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 Table\$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**
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 `metadata``KEY`. Il `KEY` `positionId` è uno `VALUE` dei `data``KEY`. Il `KEY` `accountNumber` è un `VALUE` dei `account``KEY`, e il `account` `KEY` è un `VALUE` dei `data``KEY`.
*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\$1TABLE 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\$1ACCOUNT\$1NUMBER | NUMERO\$1CONTO | ID\$1UNITÀ\$1AZIENDALE | ID\$1POSIZIONE |
| --- |--- |--- |--- |
| 32000 | 42000 | 7 | 0100 |
| 32001 | 42001 | 6 | 0090 |
**4. Postgres\$1sql\$1read\$1json**
*****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` e`VALUE`. 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 tipo`CAST`, 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"`
`CAST`Il 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 type`CAST`, è 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`: e`VALUE`: `positionId``0100`, senza virgolette doppie).
Nel caso precedente, is type to, `parentAccountNumber` is type `CAST` to`INT`, `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\$1SQL\$1JSON\$1AGGREGATION\$1JOIN**
*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 come`JSON_OBJECT`,`JSON_ARRAY`, `JSON_OBJECTAGG` e. `JSON_ARRAYAGG`
+ `JSON_OBJECT`accetta due parametri: e. `KEY` `VALUE` Il `KEY` parametro deve essere codificato o di natura statica. Il `VALUE` parametro è derivato dall'output della tabella.
+ `JSON_ARRAYAGG`accetta `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\$1SQL\$1JSON\$1AGGREGATION\$1JOIN**
Le `JSON_BUILD_OBJECT` funzioni `JSON_AGG` integrate di PostgreSQL convertono i dati a livello di riga in formato JSON. `JSON_BUILD_OBJECT`PostgreSQL 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\$1procedure\$1with\$1JSON\$1Query**
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\$1function\$1with\$1JSON\$1QUERY**
*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
}
}
]
}
```