Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.
# Convertir les requêtes Oracle JSON en base de données PostgreSQL SQL SQL SQL
*Pinesh Singal et Lokesh Gurram, Amazon Web Services*
## Résumé
Ce processus de migration pour passer d'une solution sur site au cloud Amazon Web Services (AWS) utilise l'outil AWS Schema Conversion Tool (AWS SCT) pour convertir le code d'une base de données Oracle en une base de données PostgreSQL. La majeure partie du code est automatiquement convertie par AWS SCT. Toutefois, les requêtes Oracle liées à JSON ne sont pas automatiquement converties.
À partir de la version Oracle 12.2, Oracle Database prend en charge diverses fonctions JSON qui aident à convertir les données JSON en données basées sur les lignes. Cependant, AWS SCT ne convertit pas automatiquement les données basées sur JSON dans un langage pris en charge par PostgreSQL.
Ce modèle de migration se concentre principalement sur la conversion manuelle des requêtes Oracle liées au JSON avec des fonctions telles que `JSON_OBJECT``JSON_ARRAYAGG`, et d'une base `JSON_TABLE` de données Oracle vers une base de données PostgreSQL.
## Conditions préalables et limitations
**Conditions préalables**
+ Un compte AWS actif
+ Une instance de base de données Oracle sur site (opérationnelle)
+ Une instance de base de données Amazon Relational Database Service (Amazon RDS) pour PostgreSQL ou Amazon Aurora PostgreSQL Edition compatible (opérationnelle)
**Limites**
+ Les requêtes liées au JSON nécessitent un format fixe `KEY` et un format. `VALUE` Le fait de ne pas utiliser ce format renvoie un résultat erroné.
+ Si une modification de la structure JSON ajoute de nouvelles `VALUE` paires `KEY` et de nouvelles paires dans la section des résultats, la procédure ou la fonction correspondante doit être modifiée dans la requête SQL.
+ Certaines fonctions liées au JSON sont prises en charge dans les versions antérieures d'Oracle et de PostgreSQL, mais avec moins de fonctionnalités.
**Versions du produit**
+ Oracle Database version 12.2 et versions ultérieures
+ Version 9.5 et ultérieure compatible avec Amazon RDS for PostgreSQL ou Aurora PostgreSQL
+ Dernière version d'AWS SCT (testée à l'aide de la version 1.0.664)
## Architecture
**Pile technologique source**
+ Une instance de base de données Oracle avec la version 19c
**Pile technologique cible**
+ Une instance de base de données compatible avec Amazon RDS for PostgreSQL ou Aurora PostgreSQL avec la version 13
**Architecture cible**
![\[La description suit le schéma.\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png)
1. Utilisez AWS SCT avec le code de fonction JSON pour convertir le code source d'Oracle vers PostgreSQL.
1. La conversion produit des fichiers .sql migrés compatibles avec PostgreSQL.
1. Convertissez manuellement les codes de fonction Oracle JSON non convertis en codes de fonction JSON PostgreSQL.
1. Exécutez les fichiers .sql sur l'instance de base de données cible compatible Aurora PostgreSQL.
## Outils
**Services AWS**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) est un moteur de base de données relationnelle entièrement géré conçu pour le cloud et compatible avec MySQL et PostgreSQL.
+ [Amazon Relational Database Service (Amazon RDS) pour PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) vous aide à configurer, exploiter et dimensionner une base de données relationnelle PostgreSQL dans le cloud AWS.
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) prend en charge les migrations de bases de données hétérogènes en convertissant automatiquement le schéma de base de données source et la majorité du code personnalisé dans un format compatible avec la base de données cible.
**Autres services**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) est un environnement de développement intégré qui simplifie le développement et la gestion des bases de données Oracle dans les déploiements traditionnels et basés sur le cloud.
+ pgAdmin ou. DBeaver [pgAdmin](https://www.pgadmin.org/) est un outil de gestion open source pour PostgreSQL. Il fournit une interface graphique qui vous permet de créer, de gérer et d'utiliser des objets de base de données. [DBeaver](https://dbeaver.io/)est un outil de base de données universel.
## Bonnes pratiques
La requête Oracle utilise `CAST` le type par défaut lors de l'utilisation de la `JSON_TABLE` fonction. Il est recommandé de l'utiliser également `CAST` dans PostgreSQL, en utilisant deux fois plus de caractères (). `>>`
*Pour plus d'informations, consultez *Postgres\$1SQL\$1Read\$1JSON* dans la section Informations supplémentaires.*
## Épopées
### Générez les données JSON dans les bases de données Oracle et PostgreSQL
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Stockez les données JSON dans la base de données Oracle. | Créez une table dans la base de données Oracle et stockez les données JSON dans la `CLOB` colonne. *Utilisez le script *Oracle\$1Table\$1Creation\$1Insert\$1Script qui se trouve dans la section Informations supplémentaires*.* | Ingénieur en migration |
| Stockez les données JSON dans la base de données PostgreSQL. | Créez une table dans la base de données PostgreSQL et stockez les données JSON dans la colonne. `TEXT` *Utilisez le *Postgres\$1Table\$1Creation\$1Insert\$1Script* qui se trouve dans la section Informations supplémentaires.* | Ingénieur en migration |
### Convertissez le JSON au format ROW
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Convertissez les données JSON dans la base de données Oracle. | Rédigez une requête Oracle SQL pour lire les données JSON au format ROW. *Pour plus de détails et des exemples de syntaxe, consultez *Oracle\$1SQL\$1Read\$1JSON* dans la section Informations supplémentaires.* | Ingénieur en migration |
| Convertissez les données JSON dans la base de données PostgreSQL. | Rédigez une requête PostgreSQL pour lire les données JSON au format ROW. *Pour plus de détails et des exemples de syntaxe, consultez *Postgres\$1SQL\$1Read\$1JSON* dans la section Informations supplémentaires.* | Ingénieur en migration |
### Convertissez manuellement les données JSON à l'aide de la requête SQL et rapportez le résultat au format JSON
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Effectuez des agrégations et des validations sur la requête SQL Oracle. | Pour convertir manuellement les données JSON, effectuez une jointure, une agrégation et une validation sur la requête SQL Oracle, puis rapportez le résultat au format JSON. *Utilisez le code sous *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join dans la section Informations supplémentaires*.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingénieur en migration |
| Effectuez des agrégations et des validations sur la requête SQL Postgres. | Pour convertir manuellement les données JSON, effectuez une jointure, une agrégation et une validation sur la requête PostgreSQL, puis rapportez le résultat au format JSON. *Utilisez le code situé sous *Postgres\$1SQL\$1JSON\$1Aggregation\$1Join dans la section* Informations supplémentaires.*[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingénieur en migration |
### Convertir la procédure Oracle en une fonction PostgreSQL contenant des requêtes JSON
| Sous-tâche | Description | Compétences requises |
| --- | --- | --- |
| Convertissez les requêtes JSON de la procédure Oracle en lignes. | *Pour l'exemple de procédure Oracle, utilisez la requête Oracle précédente et le code situé sous *Oracle\$1Procedure\$1with\$1JSON\$1Query* dans la section Informations supplémentaires.* | Ingénieur en migration |
| Convertissez les fonctions PostgreSQL qui comportent des requêtes JSON en données basées sur des lignes. | **Pour les exemples de fonctions PostgreSQL, utilisez la requête PostgreSQL précédente et le code qui se trouve sous Postgres\$1Function\$1with\$1JSON\$1Query dans la section Informations supplémentaires.** | Ingénieur en migration |
## Ressources connexes
+ [Fonctions Oracle JSON](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html)
+ [Fonctions JSON de PostgreSQL](https://www.postgresql.org/docs/13/functions-json.html)
+ [Exemples de fonctions Oracle JSON](https://oracle-base.com/articles/12c/sql-json-functions-12cr2)
+ [Exemples de fonctions JSON PostgreSQL](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg)
+ [Outil de conversion de schéma AWS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
## Informations supplémentaires
Pour convertir le code JSON de la base de données Oracle vers la base de données PostgreSQL, utilisez les scripts suivants, dans l'ordre.
**1. Oracle\$1Table\$1Creation\$1Insert\$1Script**
```
create table aws_test_table(id number,created_on date default sysdate,modified_on date,json_doc clob);
REM INSERTING into EXPORT_TABLE
SET DEFINE OFF;
Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc)
values (1,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{
"metadata" : {
"upperLastNameFirstName" : "ABC XYZ",
"upperEmailAddress" : "abc@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "032323323",
"displayName" : "Abc, Xyz",
"firstName" : "Xyz",
"lastName" : "Abc",
"emailAddress" : "abc@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0100",
"arrayPattern" : " -'",
"a]')
|| TO_CLOB(q'[ccount" : {
"companyId" : "SMGE",
"businessUnitId" : 7,
"accountNumber" : 42000,
"parentAccountNumber" : 32000,
"firstName" : "john",
"lastName" : "doe",
"street1" : "retOdertcaShr ",
"city" : "new york",
"postalcode" : "XY ABC",
"country" : "United States"
},
"products" : [
{
"appUserGuid" : "i0acc4450000001823fbad478e2eab8a0",
"id" : "0000000046",
]')
|| TO_CLOB(q'[ "name" : "ProView",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}]'));
Insert into aws_test_table (ID,CREATED_ON,MODIFIED_ON,json_doc) values (2,to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),to_date('02-AUG-2022 12:30:14','DD-MON-YYYY HH24:MI:SS'),TO_CLOB(q'[{
"metadata" : {
"upperLastNameFirstName" : "PQR XYZ",
"upperEmailAddress" : "pqr@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "54534343",
"displayName" : "Xyz, pqr",
"firstName" : "pqr",
"lastName" : "Xyz",
"emailAddress" : "pqr@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0090",
"arrayPattern" : " -'",
"account" : {
"companyId" : "CARS",
"busin]')
|| TO_CLOB(q'[essUnitId" : 6,
"accountNumber" : 42001,
"parentAccountNumber" : 32001,
"firstName" : "terry",
"lastName" : "whitlock",
"street1" : "UO 123",
"city" : "TOTORON",
"region" : "NO",
"postalcode" : "LKM 111",
"country" : "Canada"
},
"products" : [
{
"appUserGuid" : "ia744d7790000016899f8cf3f417d6df6",
"id" : "0000000014",
"name" : "ProView eLooseleaf",
]')
|| TO_CLOB(q'[ "domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}]'));
commit;
```
**2. Postgres\$1Table\$1Creation\$1Insert\$1Script**
```
create table aws_test_pg_table(id int,created_on date ,modified_on date,json_doc text);
insert into aws_test_pg_table(id,created_on,modified_on,json_doc)
values(1,now(),now(),'{
"metadata" : {
"upperLastNameFirstName" : "ABC XYZ",
"upperEmailAddress" : "abc@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "032323323",
"displayName" : "Abc, Xyz",
"firstName" : "Xyz",
"lastName" : "Abc",
"emailAddress" : "abc@gmail.com",
"productRegistrationStatus" : "Not registered",
"positionId" : "0100",
"arrayPattern" : " -",
"account" : {
"companyId" : "SMGE",
"businessUnitId" : 7,
"accountNumber" : 42000,
"parentAccountNumber" : 32000,
"firstName" : "john",
"lastName" : "doe",
"street1" : "retOdertcaShr ",
"city" : "new york",
"postalcode" : "XY ABC",
"country" : "United States"
},
"products" : [
{
"appUserGuid" : "i0acc4450000001823fbad478e2eab8a0",
"id" : "0000000046",
"name" : "ProView",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}');
insert into aws_test_pg_table(id,created_on,modified_on,json_doc)
values(2,now(),now(),'{
"metadata" : {
"upperLastNameFirstName" : "PQR XYZ",
"upperEmailAddress" : "pqr@gmail.com",
"profileType" : "P"
},
"data" : {
"onlineContactId" : "54534343",
"displayName" : "Xyz, pqr",
"firstName" : "pqr",
"lastName" : "Xyz",
"emailAddress" : "a*b**@h**.k**",
"productRegistrationStatus" : "Not registered",
"positionId" : "0090",
"arrayPattern" : " -",
"account" : {
"companyId" : "CARS",
"businessUnitId" : 6,
"accountNumber" : 42001,
"parentAccountNumber" : 32001,
"firstName" : "terry",
"lastName" : "whitlock",
"street1" : "UO 123",
"city" : "TOTORON",
"region" : "NO",
"postalcode" : "LKM 111",
"country" : "Canada"
},
"products" : [
{
"appUserGuid" : "ia744d7790000016899f8cf3f417d6df6",
"id" : "0000000014",
"name" : "ProView eLooseleaf",
"domain" : "EREADER",
"registrationStatus" : false,
"status" : "11"
}
]
}
}');
```
**3. Oracle\$1SQL\$1Read\$1JSON**
Les blocs de code suivants montrent comment convertir des données Oracle JSON au format de ligne.
*Exemple de requête et de syntaxe*
```
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 );
```
Le document JSON stocke les données sous forme de collections. Chaque collection peut avoir `KEY` et être `VALUE` associée. Chacun `VALUE` peut avoir des nids `KEY` et des `VALUE` paires. Le tableau suivant fournit des informations sur la lecture `VALUE` du document JSON spécifique.
|
|
| KEY | HIÉRARCHIE ou CHEMIN à utiliser pour obtenir la VALEUR | VALUE |
| --- |--- |--- |
| `profileType` | `metadata` -> `profileType` | « P » |
| `positionId` | `data` -> `positionId` | « 0100" |
| `accountNumber` | `data`-> compte -> `accountNumber` | 42000 |
Dans le tableau précédent, `KEY` `profileType` il s'agit `VALUE` d'un des `metadata``KEY`. `KEY``positionId`C'est `VALUE` l'un des `data``KEY`. Le `KEY` `accountNumber` est un `VALUE` du `account``KEY`, et le `account` `KEY` est un `VALUE` du `data``KEY`.
*Exemple de document 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"
}
]
}
}
```
*Requête SQL utilisée pour obtenir les champs sélectionnés à partir du document 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
```
Dans la requête précédente, `JSON_TABLE` il existe une fonction intégrée à Oracle qui convertit les données JSON au format de ligne. La fonction JSON\$1TABLE attend des paramètres au format JSON.
Chaque élément `COLUMNS` possède un élément prédéfini`PATH`, et un élément approprié `VALUE` pour un élément donné `KEY` est renvoyé sous forme de ligne.
*Résultat de la requête précédente*
|
|
| NUMÉRO\$1COMPTE\$1PARENT | NUMÉRO\$1COMPTE | IDENTIFIANT\$1UNITÉ\$1ENTREPRISE | IDENTIFIANT\$1POSITION |
| --- |--- |--- |--- |
| 32000 | 42000 | 7 | 0100 |
| 32001 | 42001 | 6 | 0090 |
**4. Postgres\$1SQL\$1Read\$1JSON**
*****Exemple de requête et de syntaxe*
```
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 ;
```
Dans Oracle, `PATH` est utilisé pour identifier le `KEY` et spécifique`VALUE`. Cependant, PostgreSQL utilise `HIERARCHY` un modèle pour `KEY` lire `VALUE` et à partir de JSON. Les mêmes données JSON mentionnées ci-dessous `Oracle_SQL_Read_JSON` sont utilisées dans les exemples suivants.
*Requête SQL de type CAST non autorisée*
(Si vous forcez le `CAST` texte, la requête échoue avec une erreur de syntaxe.)
```
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'utilisation d'un seul opérateur supérieur à (`>`) renverra le résultat `VALUE` défini pour cela. `KEY` Par exemple, `KEY` :`positionId`, et `VALUE` :`"0100"`.
`CAST`Le type n'est pas autorisé lorsque vous utilisez le seul opérateur supérieur à ()`>`.
*Requête SQL de type CAST autorisée*
```
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 ;
```
Pour utiliser le type`CAST`, vous devez utiliser l'opérateur double supérieur à. Si vous utilisez l'opérateur unique supérieur à, la requête renvoie le paramètre `VALUE` défini (par exemple, `KEY` :`positionId`, et `VALUE` :`"0100"`). L'utilisation de l'opérateur double supérieur à (`>>`) renvoie la valeur réelle définie pour cela `KEY` (par exemple, `KEY` :, et `VALUE` : `positionId``0100`, sans guillemets doubles).
Dans le cas précédent, `parentAccountNumber` est de type `CAST` to`INT`, `accountNumber` de type `CAST` to`INT`, `businessUnitId` de type `CAST` to `INT` et `positionId` de type `CAST` to`VARCHAR`.
Les tableaux suivants présentent les résultats des requêtes qui expliquent le rôle de l'opérateur supérieur unique (`>`) et de l'opérateur double supérieur (). `>>`
Dans le premier tableau de table, la requête utilise l'opérateur unique supérieur à ()`>`. Chaque colonne est de type JSON et ne peut pas être convertie en un autre type de données.
|
|
| parentAccountNumber | Numéro de compte | businessUnitId | Identifiant du poste |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | « 0100" |
| 2005284042 | 2005284042 | 6 | « 0090" |
| 2000272719 | 2000272719 | 1 | « 0100" |
Dans le second tableau, la requête utilise l'opérateur double supérieur à ()`>>`. Chaque colonne prend en charge le type en `CAST` fonction de la valeur de la colonne. Par exemple, `INTEGER` dans ce contexte.
|
|
| parentAccountNumber | Numéro de compte | businessUnitId | Identifiant du poste |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | 0100 |
| 2005284042 | 2005284042 | 6 | 0090 |
| 2000272719 | 2000272719 | 1 | 0100 |
**5. Oracle\$1SQL\$1JSON\$1Aggregation\$1Join**
*Exemple de requête*
```
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
);
```
Pour convertir les données au niveau des lignes au format JSON, Oracle dispose de fonctions intégrées telles que`JSON_OBJECT`, `JSON_ARRAY``JSON_OBJECTAGG`, et. `JSON_ARRAYAGG`
+ `JSON_OBJECT`accepte deux paramètres : `KEY` et`VALUE`. Le `KEY` paramètre doit être codé en dur ou de nature statique. Le `VALUE` paramètre est dérivé de la sortie de la table.
+ `JSON_ARRAYAGG`accepte `JSON_OBJECT` en tant que paramètre. Cela permet de regrouper l'ensemble d'`JSON_OBJECT`éléments sous forme de liste. Par exemple, si vous avez un `JSON_OBJECT` élément comportant plusieurs enregistrements (plusieurs `KEY` et `VALUE` paires dans le jeu de données), il `JSON_ARRAYAGG` ajoute l'ensemble de données et crée une liste. Selon le langage de structure de données, `LIST` il s'agit d'un groupe d'éléments. Dans ce contexte, `LIST` il y a un groupe d'`JSON_OBJECT`éléments.
L'exemple suivant montre un `JSON_OBJECT` élément.
```
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
```
L'exemple suivant montre deux `JSON_OBJECT` éléments, `LIST` indiqués par des accolades (`[ ]`).
```
[
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
,
{
"taxProfessionalCount": 2,
"attorneyCount": 1,
"nonAttorneyCount": 3,
"clerkCount":4
}
]
```
*Exemple de requête 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
);
```
*Exemple de résultat de la requête SQL précédente*
```
{
"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**
Les `JSON_BUILD_OBJECT` fonctions intégrées de PostgreSQL convertissent les données au niveau `JSON_AGG` des lignes au format JSON. `JSON_BUILD_OBJECT`PostgreSQL `JSON_AGG` et sont équivalents à Oracle et. `JSON_OBJECT` `JSON_ARRAYAGG`
*Exemple de requête*
```
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;
```
*Exemple de résultat de la requête précédente*
Les résultats d'Oracle et de PostgreSQL sont exactement les mêmes.
```
{
"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. Procedure\$1Oracle\$1with\$1json\$1query**
Ce code convertit la procédure Oracle en une fonction PostgreSQL dotée de requêtes SQL JSON. Il montre comment la requête transpose le JSON en lignes et inversement.
```
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;
/
```
*Exécution de la procédure*
Le bloc de code suivant explique comment exécuter la procédure Oracle créée précédemment avec un exemple d'entrée JSON dans la procédure. Il vous donne également le résultat ou le résultat de cette procédure.
```
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;
/
```
*Sortie de procédure*
```
{
"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. Fonction\$1Postgres\$1avec\$1requête JSON**
*Exemple de fonction*
```
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;
$$;
```
*Exécution de la fonction*
```
select f_pg_json_test('{
"accounts": [{
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}, {
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}]
}') ;
```
*Sortie de fonction*
La sortie suivante est similaire à la sortie de la procédure Oracle. La différence est que cette sortie est au format texte.
```
{
"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
}
}
]
}
```