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

Convertire JSON le query Oracle nel database SQL Postgre SQL

Creato da Pinesh Singal () e Lokesh Gurram () AWS AWS

Ambiente: PoC o pilota	Fonte: Database: Relazionale	Obiettivo: Amazon RDS Postgre SQL
Tipo R: Re-architect	Carico di lavoro: Oracle	Tecnologie: database; migrazione
AWSservizi: Amazon Aurora; Amazon RDS

Riepilogo

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

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

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

Prerequisiti e limitazioni

Prerequisiti

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

Limitazioni

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

Versioni del prodotto

Oracle Database versione 12.2 e successive
Amazon RDS for Postgre o SQL Aurora Postgre -Compatibile con la versione 9.5 e successive SQL
AWSSCTversione più recente (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 Postgre o SQL Aurora Postgre SQL con versione 13

Architettura Target

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

Strumenti

AWSservizi

Amazon Aurora è un motore di database relazionale completamente gestito creato per il cloud e compatibile con My SQL e Postgre. SQL
Amazon Relational Database Service (RDSAmazon) per SQL Postgre ti aiuta a configurare, gestire e scalare un database relazionale SQL Postgre nel cloud. AWS
AWSSchema Conversion Tool (AWSSCT) supporta migrazioni di database eterogenee 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 oppure. DBeaver pgAdminè uno strumento di gestione open source per SQL Postgre. 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 buona pratica consiste CAST nell'utilizzarla SQL anche in Postgre, utilizzando caratteri doppi maggiori di (). >>

Per maggiori informazioni, vedi SQLPostgres_ _Read_ nella sezione Informazioni aggiuntive. JSON

Poemi epici

Attività	Descrizione	Competenze richieste
Memorizza i JSON dati nel database Oracle.	Crea una tabella nel database Oracle e archivia i JSON dati nella `CLOB` colonna. Utilizzate Oracle_Table_Creation_Insert_Script che si trova* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione
Memorizza i JSON dati nel database Postgre. SQL	Crea una tabella nel SQL database Postgre e archivia JSON i dati 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 JSON dati nel database Oracle.	Scrivi una SQL query Oracle per leggere i JSON dati in ROW formato. Per ulteriori dettagli ed esempi di sintassi, vedere Oracle_ SQL _Read_ JSON* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione
Convertire i JSON dati nel database Postgre. SQL	Scrivi una SQL query Postgre per leggere i dati in formato. JSON ROW Per maggiori dettagli ed esempi di sintassi, vedi Postgres_ SQL JSON _Read_* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione

Attività	Descrizione	Competenze richieste
Esegui aggregazioni e convalide sulla query SQL Oracle.	Per convertire manualmente i JSON dati, esegui un'unione, un'aggregazione e una convalida sulla SQL query Oracle e riporta l'output in formato. JSON Utilizza il codice in Oracle_ SQL _ JSON _Aggregation_Join* nella sezione Informazioni aggiuntive.* JOIN— I dati JSON formattati vengono passati come parametro di input alla query. JOINViene creato un interno tra questi dati statici e i JSON dati nella tabella Oracle DB. `aws_test_table` Aggregazione con convalida: i JSON dati hanno `KEY` e `VALUE` parametri con valori come`accountNumber`, `businessUnitId` e `parentAccountNumberpositionId`, che vengono utilizzati per `SUM` le aggregazioni. `COUNT` JSONformato: dopo l'unione e l'aggregazione, i dati vengono riportati in JSON formato utilizzando e. `JSON_OBJECT` `JSON_ARRAYAGG`	Ingegnere della migrazione
Esegui aggregazioni e convalide sulla query SQL Postgres.	Per convertire manualmente i JSON dati, esegui un'unione, un'aggregazione e una convalida sulla SQL query Postgre e riporta l'output in formato. JSON Usa il codice in Postgres_ SQL _ JSON _Aggregation_Join* nella sezione Informazioni aggiuntive.* JOIN— Il JSON -formatted data (`tab1`) viene passato come parametro di input alla query della clausola. `WITH` JOINViene creato un tra questi dati statici e i JSON dati contenuti nella tabella. `tab` A JOIN viene creato anche con la `WITH` clausola, che contiene JSON dati nella `aws_test_pg_table` tabella. Aggregazione: i JSON dati hanno `KEY` e `VALUE` parametri con valori come`accountNumber`,, e `parentAccountNumber` `businessUnitIdpositionId`, che vengono utilizzati per le aggregazioni `SUM` e`COUNT`. JSONformato: dopo l'unione e l'aggregazione, i dati vengono riportati in JSON formato utilizzando e. `JSON_BUILD_OBJECT` `JSON_AGG`	Ingegnere della migrazione

Attività	Descrizione	Competenze richieste
Convertire le JSON interrogazioni della procedura Oracle in righe.	Per la procedura Oracle di esempio, utilizzare la precedente query Oracle e il codice in Oracle_procedure_with_ JSON _Query* nella sezione Informazioni aggiuntive.*	Ingegnere della migrazione
Converti le SQL funzioni di Postgre che contengono JSON interrogazioni in dati basati su righe.	Per le SQL funzioni Postgre di esempio, usa la precedente query Postgre e il codice che si trova in Postgres_function_with_ SQL _Query nella sezione Informazioni aggiuntive. JSON	Ingegnere della migrazione

Risorse correlate

Informazioni aggiuntive

Per convertire il JSON codice 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_ _Letti_ SQL 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 JSON documento memorizza i dati come raccolte. Ogni collezione può avere KEY e VALUE abbinarsi. Ognuno VALUE può avere nidi KEY e VALUE coppie. La tabella seguente fornisce informazioni sulla lettura delle specifiche VALUE del JSON documento.

KEY	HIERARCHYo PATH da utilizzare per ottenere il VALUE	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.

JSONDocumento di esempio


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

SQLinterrogazione che viene utilizzata per ottenere i campi selezionati dal JSON documento


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 JSON dati in formato riga. La TABLE funzione JSON _ prevede parametri in JSON formato.

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

Risultato della query precedente

PARENT_ACCOUNT_NUMBER	ACCOUNT_NUMBER	BUSINESS_ UNIT _ID	POSITION_ID
32000	42000	7	0100
32001	42001	6	0090

4. Postgres_ _Leggi_ SQL JSON

Esempio di interrogazione e sintassi


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, Postgre SQL utilizza un HIERARCHY modello per la lettura KEY e VALUE la trasmissione. JSON Gli stessi JSON dati menzionati di seguito Oracle_SQL_Read_JSON vengono utilizzati negli esempi seguenti.

SQLinterrogazione con tipo CAST non consentita

(Se si forza la digitazioneCAST, 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 (). >

SQLinterrogazione con tipo consentito 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 è JSON di tipo e non può essere convertita in un altro tipo di dati.

parentAccountNumber	accountNumber	businessUnitId	positionId
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	accountNumber	businessUnitId	positionId
2003565430	2003564830	7	0100
2005284042	2005284042	6	0090
2000272719	2000272719	1	0100

5. SQLOracle_ _ _Aggregazione_Join JSON

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 JSON formato, Oracle dispone di funzioni integrate comeJSON_OBJECT, JSON_ARRAYJSON_OBJECTAGG, e. JSON_ARRAYAGG

JSON_OBJECTaccetta due parametri: KEY e. 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
      }
]

Query di esempio 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 SQL query precedente


{
  "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. SQLPostgres_ _ _Aggregazione_Partecipa JSON

Le SQL funzioni integrate di Postgre convertono i dati a livello di C in formato. JSON_BUILD_OBJECT JSON_AGG ROW JSON Postgre e sono equivalenti a JSON_AGG Oracle SQL JSON_BUILD_OBJECT 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 Postgre SQL è 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. Procedura_oracle_con_ _Query JSON

Questo codice converte la procedura Oracle in una funzione Postgre con interrogazioni. SQL JSON SQL Mostra come la query si 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 JSON di input 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. JSON PostgreS _funzione_con_ _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 SSH tunnel in pgAdmin

Copia le tabelle Amazon DynamoDB tra più account