As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá. # Converta consultas JSON Oracle em SQL do banco de dados PostgreSQL *Pinesh Singal e Lokesh Gurram, Amazon Web Services* ## Resumo Esse processo de migração do on-premises para a nuvem da Amazon Web Services (AWS) usa a AWS Schema Conversion Tool (AWS SCT) para converter o código de um banco de dados Oracle em um banco de dados PostgreSQL. A maior parte do código é convertida automaticamente pela AWS SCT. No entanto, as consultas Oracle relacionadas ao JSON não são convertidas automaticamente. A partir da versão Oracle 12.2, o Oracle Database suporta várias funções JSON que ajudam na conversão de dados baseados em JSON em dados baseados em ROW. No entanto, a AWS SCT não converte automaticamente dados baseados em JSON em linguagem compatível com o PostgreSQL. Esse padrão de migração se concentra principalmente na conversão manual das consultas Oracle relacionadas ao JSON com funções como `JSON_OBJECT`, `JSON_ARRAYAGG` e `JSON_TABLE` de um banco de dados Oracle para um banco de dados PostgreSQL. ## Pré-requisitos e limitações **Pré-requisitos ** + Uma conta AWS ativa + Uma instância do banco de dados Oracle on-premises (em funcionamento) + Uma instância de banco de dados do Amazon Relational Database Service (Amazon RDS) para PostgreSQL ou Amazon Aurora Edição Compatível com PostgreSQL (em funcionamento) **Limitações** + As consultas relacionadas ao JSON exigem um formato fixo de `KEY` e `VALUE`. Não usar esse formato retorna o resultado errado. + Se alguma alteração na estrutura JSON adicionar novos pares `KEY` e `VALUE` na seção de resultados, o procedimento ou função correspondente deverá ser alterado na consulta SQL. + Algumas funções relacionadas ao JSON são suportadas em versões anteriores do Oracle e do PostgreSQL, mas com menos recursos. **Versões do produto** + Oracle Database versão 12.2 e posterior + Amazon RDS para PostgreSQL ou Aurora compatível com PostgreSQL versão 9.5 e posterior + Versão mais recente da AWS SCT (testada usando a versão 1.0.664) ## Arquitetura **Pilha de tecnologia de origem** + Uma instância de banco de dados Oracle com a versão 19c **Pilha de tecnologias de destino** + Uma instância de banco de dados do Amazon RDS para PostgreSQL ou Aurora compatível com PostgreSQL com a versão 13 **Arquitetura de destino** ![\[A descrição segue o diagrama.\]](http://docs.aws.amazon.com/pt_br/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png) 1. Use a AWS SCT com o código da função JSON para converter o código-fonte do Oracle para o PostgreSQL. 1. A conversão produz arquivos .sql migrados compatíveis com o PostgreSQL. 1. Converta manualmente os códigos de função Oracle JSON não convertidos em códigos de função JSON do PostgreSQL. 1. Execute os arquivos .sql na instância de banco de dados de destino do Aurora compatível com o PostgreSQL. ## Ferramentas **Serviços da AWS** + O [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) é um mecanismo de banco de dados relacional totalmente gerenciado que é construído para a nuvem e compatível com o MySQL e o PostgreSQL. + O [Amazon Relational Database Service (Amazon RDS) para PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) ajuda você a configurar, operar e escalar um banco de dados relacional PostgreSQL na Nuvem AWS. + O [AWS Schema Conversion Tool (AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)) oferece suporte a migrações heterogêneas de bancos de dados convertendo automaticamente o esquema do banco de dados de origem e a maior parte do código personalizado em um formato compatível com o banco de dados de destino. **Outros serviços** + O [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) é um ambiente de desenvolvimento integrado que simplifica o desenvolvimento e o gerenciamento de bancos de dados Oracle em implantações tradicionais e baseadas em nuvem. + pgAdmin ou. DBeaver [O pgAdmin](https://www.pgadmin.org/) é uma ferramenta de gerenciamento de código aberto para o PostgreSQL. Ele fornece uma interface gráfica que ajuda você a criar, manter e usar objetos de banco de dados. [DBeaver](https://dbeaver.io/)é uma ferramenta de banco de dados universal. ## Práticas recomendadas A consulta Oracle tem o tipo `CAST` como padrão ao usar a função `JSON_TABLE`. Uma prática recomendada é usar `CAST` no PostgreSQL também, usando caracteres duplos maiores que caracteres (`>>`). Para obter mais informações, consulte *Postgres\$1SQL\$1Read\$1JSON* na seção *Informações adicionais*. ## Épicos ### Gere os dados JSON nos bancos de dados Oracle e PostgreSQL | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Armazene os dados JSON no banco de dados Oracle. | Crie uma tabela no banco de dados Oracle e armazene os dados JSON na coluna `CLOB`.  Use o *Oracle\$1Table\$1Creation\$1Insert\$1Script* que está na seção *Informações adicionais*. | Engenheiro de migração | | Armazene os dados JSON no banco de dados PostgreSQL. | Crie uma tabela no banco de dados PostgreSQL e armazene os dados JSON na coluna `TEXT`. Use o *Postgres\$1Table\$1Creation\$1Insert\$1Script* que está na seção *Informações adicionais*. | Engenheiro de migração | ### Converta o JSON em formato ROW | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Converta os dados JSON no banco de dados Oracle. | Escreva uma consulta Oracle SQL para ler os dados JSON no formato ROW. Para obter mais detalhes e exemplos de sintaxe, consulte *Oracle\$1SQL\$1Read\$1JSON* na seção *Informações adicionais*. | Engenheiro de migração | | Converta os dados JSON no banco de dados PostgreSQL. | Escreva uma consulta PostgreSQL para ler os dados JSON no formato ROW. Para obter mais detalhes e exemplos de sintaxe, consulte *Postgres\$1SQL\$1Read\$1JSON* na seção *Informações adicionais*. | Engenheiro de migração | ### Converta manualmente os dados JSON usando a consulta SQL e relate a saída no formato JSON | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Execute agregações e validação na consulta Oracle SQL. | Para converter manualmente os dados JSON, execute uma junção, agregação e validação na consulta Oracle SQL e relate a saída no formato JSON. Use o código em *Oracle\$1SQL\$1JSON\$1Aggregation\$1Join* na seção *Informações adicionais*.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Engenheiro de migração | | Execute agregações e validação na consulta Postgres SQL. | Para converter manualmente os dados JSON, execute uma junção, agregação e validação na consulta PostgreSQL e relate a saída no formato JSON. Use o código em *Postgres\$1SQL\$1JSON\$1aggregation\$1join* na seção *Informações adicionais*.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/pt_br/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Engenheiro de migração | ### Converta o procedimento Oracle em uma função PostgreSQL que contém consultas JSON | Tarefa | Description | Habilidades necessárias | | --- | --- | --- | | Converta as consultas JSON no procedimento Oracle em linhas. | Para o exemplo de procedimento Oracle, use a consulta Oracle anterior e o código em *Oracle\$1Procedure\$1with\$1JSON\$1Query* na seção *Informações adicionais*. | Engenheiro de migração | | Converta as funções do PostgreSQL que têm consultas JSON em dados baseados em linhas. | Para os exemplos de funções do PostgreSQL, use a consulta anterior do PostgreSQL e o código que está em *Postgres\$1Function\$1with\$1JSON\$1Query* na seção *Informações adicionais*. | Engenheiro de migração | ## Recursos relacionados + [Funções Oracle JSON](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html) + [Funções JSON do PostgreSQL](https://www.postgresql.org/docs/13/functions-json.html) + [Exemplos de funções Oracle JSON](https://oracle-base.com/articles/12c/sql-json-functions-12cr2) + [Exemplos de funções JSON do PostgreSQL](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg) + [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) ## Mais informações Para converter o código JSON do banco de dados Oracle para o banco de dados PostgreSQL, use os scripts a seguir, em ordem. **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** Os blocos de código a seguir mostram como converter dados Oracle JSON em formato de linha. *Exemplo de consulta e sintaxe* ``` 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 ); ``` O documento JSON armazena os dados como coleções. Cada coleção pode ter pares de `KEY` e `VALUE`. Todos os `VALUE` podem ter pares de `KEY` e `VALUE` aninhados. A tabela a seguir fornece informações sobre como ler o `VALUE` específico do documento JSON. | | | CHAVE | HIERARCHY ou PATH a ser usado para obter o VALUE | VALUE | | --- |--- |--- | | `profileType` | `metadata` -> `profileType` | "P" | | `positionId` | `data` -> `positionId` | "0100" | | `accountNumber` | `data` -> account -> `accountNumber` | 42000 | Na tabela anterior, o `KEY` `profileType` é um `VALUE` dos `metadata` `KEY`. O `KEY` `positionId` é um `VALUE` da `data` `KEY`. O `KEY` `accountNumber` é um `VALUE` da `account` `KEY`, e a `account` `KEY` é um `VALUE` da `data` `KEY`. *Exemplo de 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" } ] } } ``` *Consulta SQL usada para obter os campos selecionados do 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 ``` Na consulta anterior, `JSON_TABLE` é uma função embutida no Oracle que converte os dados JSON em formato de linha. A função JSON\$1TABLE espera parâmetros no formato JSON. Cada item em `COLUMNS` tem um `PATH` predefinido, e um `VALUE` apropriado para uma determinada `KEY` é retornado em formato de linha. *Resultado da consulta anterior* | | | PARENT\$1ACCOUNT\$1NUMBER | ACCOUNT\$1NUMBER | BUSINESS\$1UNIT\$1ID | POSITION\$1ID | | --- |--- |--- |--- | | 32000 | 42000 | 7 | 0100 | | 32001 | 4/2001 | 6 | 0090 | **4. Postgres\$1SQL\$1Read\$1JSON** ** ***Exemplo de consulta e sintaxe* ``` 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 ; ``` No Oracle, o `PATH` é usado para identificar o `KEY` e `VALUE` específicos. No entanto, o PostgreSQL usa um modelo `HIERARCHY` para leitura de `KEY` e `VALUE` a partir do JSON. Os mesmos dados JSON mencionados abaixo de `Oracle_SQL_Read_JSON` são usados nos exemplos a seguir. *Consulta SQL com tipo CAST não permitida* (Se você forçar o tipo `CAST`, a consulta falhará com um erro de sintaxe.) ``` 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 ; ``` Usar um único operador maior que (`>`) retornará o `VALUE` definido para essa `KEY`. Por exemplo, `KEY`: `positionId`, e `VALUE`: `"0100"`. O tipo `CAST` não é permitido quando você usa o único operador maior que (`>`). *Consulta SQL com tipo CAST permitida* ``` 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 ; ``` Para usar o tipo `CAST`, você deve usar o operador duplo maior que. Se você usar o único operador maior que, a consulta retornará o `VALUE` definido (por exemplo `KEY`: `positionId` e `VALUE`: `"0100"`). Usar o operador duplo maior que (`>>`) retornará o valor real definido para essa `KEY` (por exemplo, `KEY`: `positionId` e `VALUE`: `0100`, sem aspas duplas). No caso anterior, o `parentAccountNumber` é do tipo `CAST` para `INT`, `accountNumber` é do tipo `CAST` para `INT`, `businessUnitId` é do tipo `CAST` para `INT` e `positionId` é do tipo `CAST` para `VARCHAR`. As tabelas a seguir mostram os resultados da consulta que explicam o papel do único operador maior que (`>`) e do operador duplo maior que (`>>`). Na primeira tabela, a consulta usa o único operador maior que (`>`). Cada coluna está no tipo JSON e não pode ser convertida em outro tipo de dados. | | | parentAccountNumber | accountNumber | businessUnitId | positionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | "0100" | | 2005 284042 | 2005 284042 | 6 | “0090” | | 2000272719 | 2000272719 | 1 | "0100" | Na segunda tabela, a consulta usa o operador duplo maior que (`>>`). Cada coluna oferece suporte ao tipo `CAST` com base no valor da coluna. Por exemplo, `INTEGER` neste contexto. | | | parentAccountNumber | accountNumber | businessUnitId | positionId | | --- |--- |--- |--- | | 2003565430 | 2003564830 | 7 | 0100 | | 2005 284042 | 2005 284042 | 6 | 0090 | | 2000272719 | 2000272719 | 1 | 0100 | **5. Oracle\$1SQL\$1JSON\$1Aggregation\$1Join** *Consulta de exemplo* ``` 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 ); ``` Para converter os dados em nível de linha no formato JSON, o Oracle tem funções integradas como `JSON_OBJECT`, `JSON_ARRAY`, `JSON_OBJECTAGG` e `JSON_ARRAYAGG`. + `JSON_OBJECT` aceita dois parâmetros: `KEY` e `VALUE`. O parâmetro `KEY` deve ser codificado ou de natureza estática. O parâmetro `VALUE` é derivado da saída da tabela. + O `JSON_ARRAYAGG` aceita `JSON_OBJECT` como parâmetro. Isso ajuda a agrupar o conjunto de elementos `JSON_OBJECT` como uma lista. Por exemplo, se você tiver um elemento `JSON_OBJECT` que tenha vários registros (vários pares de `KEY` e `VALUE` no conjunto de dados), o `JSON_ARRAYAGG` anexa o conjunto de dados e cria uma lista. De acordo com a linguagem Data Structure, `LIST` é um grupo de elementos. Nesse contexto, `LIST` é um grupo de elementos `JSON_OBJECT`. O exemplo a seguir mostra um elemento `JSON_OBJECT`. ``` { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } ``` O próximo exemplo mostra dois elementos `JSON_OBJECT`, com `LIST` indicado por colchetes (`[ ]`). ``` [ { "taxProfessionalCount": 0, "attorneyCount": 0, "nonAttorneyCount": 1, "clerkCount": 0 } , { "taxProfessionalCount": 2, "attorneyCount": 1, "nonAttorneyCount": 3, "clerkCount":4 } ] ``` *Exemplo de consulta 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 ); ``` *Exemplo de saída da consulta SQL anterior* ``` { "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** As funções incorporadas do PostgreSQL `JSON_BUILD_OBJECT` e `JSON_AGG` convertem os dados em nível de LINHA no formato JSON.  A `JSON_BUILD_OBJECT` e `JSON_AGG` do PostgreSQL são equivalentes à `JSON_OBJECT` e `JSON_ARRAYAGG` do Oracle. *Consulta de exemplo* ``` 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; ``` *Exemplo de saída da consulta anterior* A saída do Oracle e do PostgreSQL é exatamente a mesma. ``` { "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** Esse código converte o procedimento Oracle em uma função PostgreSQL que tem consultas SQL JSON. Mostra como a consulta transpõe o JSON em linhas e vice-versa. ``` 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; / ``` *Executando o procedimento* O bloco de código a seguir explica como você pode executar o procedimento Oracle criado anteriormente com um exemplo de entrada JSON para o procedimento. Também fornece o resultado ou a saída desse procedimento. ``` 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; / ``` *Saída do procedimento* ``` { "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** *Exemplos de função* ``` 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; $$; ``` *Execução da função* ``` select f_pg_json_test('{ "accounts": [{ "accountNumber": 42001, "parentAccountNumber": 32001, "businessUnitId": 6 }, { "accountNumber": 42000, "parentAccountNumber": 32000, "businessUnitId": 7 }] }') ; ``` *Saída da função* A saída a seguir é semelhante à saída do procedimento Oracle. A diferença é que essa saída está no formato de texto. ``` { "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 } } ] } ```