Las traducciones son generadas a través de traducción automática. En caso de conflicto entre la traducción y la version original de inglés, prevalecerá la version en inglés.
# Convertir consultas JSON de Oracle en SQL de bases de datos PostgreSQL
*Pinesh Singal y Lokesh Gurram, Amazon Web Services*
## Resumen
Este proceso de migración para pasar del entorno local a la nube de Amazon Web Services (AWS) utiliza la herramienta de conversión de esquemas de AWS (AWS SCT) para convertir el código de una base de datos Orqacle en una base de datos PostgreSQL. AWS SCT convierte automáticamente la mayor parte del código. Sin embargo, las consultas de Oracle relacionadas con JSON no se convierten automáticamente.
A partir de la versión 12.2 de Oracle, Oracle Database admite varias funciones de JSON que ayudan a convertir los datos basados en JSON en datos basados en filas. Sin embargo, AWS SCT no convierte automáticamente los datos basados en JSON a un lenguaje compatible con PostgreSQL.
Este patrón de migración se centra principalmente en convertir manualmente las consultas de Oracle relacionadas con JSON con funciones como `JSON_OBJECT`, `JSON_ARRAYAGG`, y `JSON_TABLE` de una base de datos de Oracle a una base de datos PostgreSQL.
## Requisitos previos y limitaciones
**Requisitos previos **
+ Una cuenta de AWS activa
+ Una instancia de base de datos de Oracle local (en funcionamiento)
+ Una instancia de base de datos de Amazon Relational Database Service (Amazon RDS) para la Edición compatible con PostgreSQL o Amazon Aurora
**Limitaciones**
+ Las consultas relacionadas con JSON requieren un formato AND fijo `KEY` y `VALUE`. Si no se utiliza ese formato, se obtiene un resultado incorrecto.
+ Si algún cambio en la estructura de JSON añade pares nuevos `KEY` y `VALUE`en la sección de resultados, se debe cambiar el procedimiento o la función correspondiente en la consulta SQL.
+ Algunas funciones relacionadas con JSON se admiten en versiones anteriores de Oracle y PostgreSQL, pero con menos capacidades.
**Versiones de producto**
+ Oracle Database, versión 12.2 y posterior
+ Amazon RDS para PostgreSQL o Aurora, compatible con PostgreSQL, versión 9.5 y versiones posteriores
+ Última versión de AWS SCT (probadas con la versión 1.0.664)
## Arquitectura
**Pila de tecnología de origen**
+ Una instancia de base de datos de Oracle con la versión 19c
**Pila de tecnología de destino**
+ Una instancia de base de datos compatible con Amazon RDS para PostgreSQL o Aurora PostgreSQL con la versión 13
**Arquitectura de destino**
![\[La descripción sigue el diagrama.\]](http://docs.aws.amazon.com/es_es/prescriptive-guidance/latest/patterns/images/pattern-img/5e2c3b07-9ef5-417f-b049-bcea58f2c3ec/images/2ff8b00b-8849-4ef1-9be1-579f7b51be10.png)
1. Utilice AWS SCT con el código de función JSON para convertir el código fuente de Oracle a PostgreSQL.
1. La conversión produce archivos.sql migrados compatibles con PostgreSQL.
1. Convierta manualmente los códigos de función JSON de Oracle no convertidos en códigos de función JSON de PostgreSQL.
1. Ejecute los archivos.sql en la instancia de base de datos compatible con PostgreSQL de Aurora de destino.
## Tools (Herramientas)
**Servicios de AWS**
+ [Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/CHAP_AuroraOverview.html) es un motor de base de datos relacional completamente administrado diseñado para la nube y compatible con MySQL y PostgreSQL.
+ [Amazon Relational Database Service (Amazon RDS) para PostgreSQL](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html) le ayuda a configurar, utilizar y escalar una base de datos relacional de PostgreSQL en la nube de AWS.
+ La [Herramienta de conversión de esquemas de AWS (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) simplifica las migraciones de bases de datos heterogéneas al convertir automáticamente el esquema de la base de datos de origen y la mayor parte del código personalizado, lo que incluye las vistas, los procedimientos almacenados y las funciones, a un formato compatible con la base de datos de destino.
**Otros servicios**
+ [Oracle SQL Developer](https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html) es un entorno de desarrollo integrado que simplifica el desarrollo y la administración de bases de datos de Oracle, tanto en implementaciones tradicionales como en implementaciones basadas en la nube.
+ pgAdmin o. DBeaver [pgAdmin](https://www.pgadmin.org/) es una herramienta de gestión de código abierto para PostgreSQL. Proporciona una interfaz gráfica que le ayuda a crear, mantener y utilizar objetos de bases de datos. [DBeaver](https://dbeaver.io/)es una herramienta de base de datos universal.
## Prácticas recomendadas
La consulta de Oracle tiene el tipo `CAST` como valor predeterminado cuando se utiliza la función `JSON_TABLE`. Una buena práctica es utilizarla también `CAST` en PostgreSQL, utilizando caracteres dobles mayores que (`>>`).
Para obtener más información, consulte *Postgres\$1SQL\$1read\$1JSON* en la sección *Información adicional.*
## Epics
### Genere los datos JSON en las bases de datos de Oracle y PostgreSQL
| Tarea | Descripción | Habilidades requeridas |
| --- | --- | --- |
| Guarde los datos JSON en la base de datos de Oracle. | Cree una tabla en la base de datos de Oracle y almacene los datos JSON en la columna `CLOB`. Utilice el *Oracle\$1Table\$1Creation\$1Insert\$1Script* que se encuentra en la sección *Información adicional*. | Ingeniero de migraciones |
| Guarde los datos JSON en la base de datos PostgreSQL. | Cree una tabla en la base de datos PostgreSQL y almacene los datos JSON en la columna `TEXT`. Utilice el archivo *Postgres\$1Table\$1Creation\$1Insert\$1Script* que se encuentra en la sección *Información adicional*. | Ingeniero de migraciones |
### Convierte el JSON al formato ROW
| Tarea | Descripción | Habilidades requeridas |
| --- | --- | --- |
| Convertir los datos JSON en la base de datos de Oracle. | Escriba una consulta SQL de Oracle para leer los datos JSON en formato ROW. Para obtener más detalles y ejemplos de sintaxis, consulte *Oracle\$1SQL\$1read\$1JSON* en la sección *Información adicional*. | Ingeniero de migraciones |
| Convierta los datos JSON en la base de datos PostgreSQL. | Escriba una consulta de PostgreSQL para leer los datos JSON en formato ROW. Para obtener más detalles y ejemplos de sintaxis, consulte *Oracle\$1SQL\$1read\$1JSON* en la sección *Información adicional*. | Ingeniero de migraciones |
### Convertir manualmente los datos JSON mediante la consulta SQL e informe el resultado en formato JSON
| Tarea | Descripción | Habilidades requeridas |
| --- | --- | --- |
| Realizar agregaciones y validaciones en la consulta SQL de Oracle. | Para convertir manualmente los datos de JSON, realice una unión, agregación y validación en la consulta SQL de Oracle e informe el resultado en formato JSON. Utilice el código que aparece en *Oracle\$1SQL\$1JSON\$1Aggregation\$1JOIN* en la sección *Información adicional*.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/es_es/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingeniero de migraciones |
| Realice agregaciones y validaciones en la consulta SQL de Postgres. | Para convertir manualmente los datos de JSON, realice una unión, agregación y validación en la consulta de PostgreSQL e informe el resultado en formato JSON. Use el código que aparece en *Postgres\$1SQL\$1JSON\$1AGGREGATION\$1JOIN* en la sección *Información adicional*.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/es_es/prescriptive-guidance/latest/patterns/convert-json-oracle-queries-into-postgresql-database-sql.html) | Ingeniero de migraciones |
### Convertir el procedimiento de Oracle en una función de PostgreSQL que contenga consultas JSON
| Tarea | Descripción | Habilidades requeridas |
| --- | --- | --- |
| Convierta las consultas JSON del procedimiento de Oracle en filas. | Para el procedimiento de Oracle de ejemplo, utilice la consulta de Oracle anterior y el código de *Oracle\$1Procedure\$1with\$1JSON\$1Query* en la sección *Información adicional*. | Ingeniero de migraciones |
| Convierta las funciones de PostgreSQL que tienen consultas JSON en datos basados en filas. | Para las funciones de PostgreSQL de ejemplo, utilice la consulta de PostgreSQL anterior y el código que se encuentra en *Postgres\$1function\$1with\$1JSON\$1Query* en la sección *Información adicional*. | Ingeniero de migraciones |
## Recursos relacionados
+ [Funciones JSON de Oracle](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html)
+ [Funciones JSON de PostgreSQL](https://www.postgresql.org/docs/13/functions-json.html)
+ [Ejemplos de funciones JSON de Oracle](https://oracle-base.com/articles/12c/sql-json-functions-12cr2)
+ [Ejemplos de funciones JSON de PostgreSQL](https://dba.stackexchange.com/questions/69655/select-columns-inside-json-agg)
+ [Herramienta de conversión de esquemas de AWS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
## Información adicional
Para convertir el código JSON de la base de datos de Oracle a la base de datos PostgreSQL, utilice los siguientes scripts en orden.
**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**
Los siguientes bloques de código muestran cómo convertir los datos JSON de Oracle a formato de fila.
*Ejemplo de consulta y sintaxis*
```
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 );
```
El documento JSON almacena los datos como colecciones. Cada colección puede tener pares `KEY` y `VALUE`. Cada `VALUE` puede tener anidados pares `KEY` y `VALUE`. En la siguiente tabla se proporciona información sobre la lectura del `VALUE` específico del documento JSON.
|
|
| KEY | HIERARCHY or PATH to be used to get the VALUE | VALUE |
| --- |--- |--- |
| `profileType` | `metadata` -> `profileType` | «P» |
| `positionId` | `data` -> `positionId` | «0100» |
| `accountNumber` | `data` -> account -> `accountNumber` | 42000 |
En la tabla anterior, `KEY` `profileType` es una `VALUE` de las `metadata` `KEY`. El `KEY` `positionId` es un `VALUE` de los `data` `KEY`. El `KEY` `accountNumber` es un `VALUE` de los `account``KEY`, y el `account` `KEY` es un `VALUE` de los `data``KEY`.
*Ejemplo 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 que se utiliza para obtener los campos seleccionados del 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
```
En la consulta anterior, `JSON_TABLE` es una función integrada en Oracle que convierte los datos JSON en formato de fila. La función JSON\$1TABLE espera parámetros en formato JSON.
Cada elemento `COLUMNS` tiene un valor predefinido `PATH`, y cuando hay un `VALUE` adecuado para un determinado elemento `KEY`, se devuelve en formato de fila.
*Resultado de la consulta anterior*
|
|
| PARENT\$1ACCOUNT\$1NUMBER | ACCOUNT\$1NUMBER | BUSINESS\$1UNIT\$1ID | POSITION\$1ID |
| --- |--- |--- |--- |
| 32000 | 42000 | 7 | 0100 |
| 32001 | 42001 | 6 | 0090 |
**4. Postgres\$1SQL\$1READ\$1JSON**
** ***Ejemplo de consulta y sintaxis*
```
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 ;
```
En Oracle, `PATH` se utiliza para identificar el `KEY` y `VALUE` especifico. Sin embargo, PostgreSQL utiliza un modelo `HIERARCHY` para leer `KEY` y `VALUE` desde JSON. Los mismos datos de JSON que se mencionan en `Oracle_SQL_Read_JSON` se utilizan en los siguientes ejemplos.
*No se admiten consultas SQL de tipo CAST*
(Si fuerza el tipo de texto `CAST`, la consulta fallará y se producirá un error de sintaxis).
```
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 ;
```
Si se utiliza un operador una vez mayor (`>`), se devolverá el `VALUE` definido para ese `KEY`. Por ejemplo, `KEY`: `positionId`, y `VALUE`: `"0100"`.
No se permite escribir el tipo `CAST` cuando se utiliza el operador una vez mayor (`>`).
*Se admiten consultas SQL de tipo CAST*
```
select *
from (
select (json_doc::json->'data'->'account'->>'parentAccountNumber')::INTEGER as parentAccountNumber,
(json_doc::json->'data'->'account'->>'accountNumber')::INTEGER as accountNumber,
(json_doc::json->'data'->'account'->>'businessUnitId')::INTEGER as businessUnitId,
(json_doc::json->'data'->>'positionId')::varchar as positionId
from aws_test_pg_table) d ;
```
Para usar el tipo `CAST`, debe usar un operador mayor del doble. Si utiliza el operador una vez mayor, la consulta devuelve el `VALUE` definido (por ejemplo `KEY`: `positionId`, y `VALUE`: `"0100"`). Si se utiliza el operador mayor del doble (`>>`), se devolverá el valor real definido para ese valor `KEY` (por ejemplo, `KEY`: `positionId`, y `VALUE`: `0100` sin comillas dobles).
En el caso anterior, `parentAccountNumber` es el tipo `CAST` a `INT`, `accountNumber` es el tipo `CAST` a `INT`, `businessUnitId` es el tipo `CAST` a `INT`, y `positionId` es el tipo `CAST` a `VARCHAR`.
En las tablas siguientes se muestran los resultados de las consultas que explican el papel del operador único mayor que (`>`) y del operador doble mayor que (`>>`).
En la primera tabla, la consulta utiliza el único operador mayor que (`>`). Cada columna es de tipo JSON y no se puede convertir en otro tipo de datos.
|
|
| parentAccountNumber | Número de cuenta | businessUnitId | ID de puesto |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | «0100» |
| 2005284042 | 2005284042 | 6 | “0090” |
| 2000272719 | 2000272719 | 1 | «0100» |
En la segunda tabla, la consulta utiliza el operador doble mayor que (`>>`). Cada columna admite el tipo `CAST` en función del valor de la columna. Por ejemplo, en este caso `INTEGER`.
|
|
| parentAccountNumber | Número de cuenta | businessUnitId | ID de puesto |
| --- |--- |--- |--- |
| 2003565430 | 2003564830 | 7 | 0100 |
| 2005284042 | 2005284042 | 6 | 0090 |
| 2000272719 | 2000272719 | 1 | 0100 |
**5. Oracle\$1SQL\$1JSON\$1AGGREGATION\$1JOIN**
*Consulta de ejemplo*
```
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 convertir los datos de nivel de fila al formato JSON, Oracle tiene funciones integradas como `JSON_OBJECT`, `JSON_ARRAY`, `JSON_OBJECTAGG` y `JSON_ARRAYAGG`.
+ `JSON_OBJECT` acepta dos parámetros: `KEY` y `VALUE`. El parámetro `KEY` debe estar codificado de forma rígida o ser de naturaleza estática. El parámetro `VALUE` se deriva de la salida de la tabla.
+ `JSON_ARRAYAGG` acepta `JSON_OBJECT` como parámetro. Esto ayuda a agrupar el conjunto de `JSON_OBJECT` elementos en forma de lista. Por ejemplo, si tiene un elemento `JSON_OBJECT` que tiene varios registros (múltiples pares `KEY` y `VALUE`en el conjunto de datos), `JSON_ARRAYAGG` agrega el conjunto de datos y crea una lista. Según el lenguaje de estructura de datos, `LIST` es un grupo de elementos. En este contexto, `LIST` es un grupo de elementos `JSON_OBJECT`.
En el siguiente ejemplo, se muestra un elemento `JSON_OBJECT`.
```
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
```
En el siguiente ejemplo, se muestran dos elementos `JSON_OBJECT`, con `LIST` indicado mediante llaves cuadradas (`[ ]`).
```
[
{
"taxProfessionalCount": 0,
"attorneyCount": 0,
"nonAttorneyCount": 1,
"clerkCount": 0
}
,
{
"taxProfessionalCount": 2,
"attorneyCount": 1,
"nonAttorneyCount": 3,
"clerkCount":4
}
]
```
*Ejemplo 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
);
```
*Ejemplo de resultado de la 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**
Las funciones integradas de PostgresQL `JSON_BUILD_OBJECT` y `JSON_AGG` convierten los datos de nivel de fila a formato JSON. PostgreSQL `JSON_OBJECT` y `JSON_AGG`son equivalentes a Oracle `JSON_BUILD_OBJECT` y `JSON_ARRAYAGG`.
*Consulta de ejemplo*
```
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;
```
*Ejemplo de salida de la consulta anterior*
Las salidas de Oracle y PostgreSQL son exactamente las mismas.
```
{
"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**
Este código convierte el procedimiento de Oracle en una función de PostgreSQL que tiene consultas JSON SQL. Muestra cómo la consulta transpone JSON a filas y 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;
/
```
*Ejecutando el procedimiento*
El siguiente bloque de código explica cómo puede ejecutar el procedimiento de Oracle creado anteriormente con una entrada JSON de ejemplo en el procedimiento. También proporciona el resultado o la salida de este procedimiento.
```
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;
/
```
*Resultado del procedimiento*
```
{
"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**
*Función de ejemplo*
```
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;
$$;
```
*Ejecución de la función*
```
select f_pg_json_test('{
"accounts": [{
"accountNumber": 42001,
"parentAccountNumber": 32001,
"businessUnitId": 6
}, {
"accountNumber": 42000,
"parentAccountNumber": 32000,
"businessUnitId": 7
}]
}') ;
```
*Salida de función*
La siguiente salida de es similar a la salida del procedimiento de Oracle. La diferencia es que esta salida está en 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
}
}
]
}
```