Ejemplos de uso de datos semiestructurados en Amazon Redshift

Los siguientes ejemplos muestran cómo trabajar con datos semiestructurados en Amazon Redshift mediante la sintaxis de PartiQL. Creará una tabla de ejemplo para cargar un conjunto de ejemplos de datos semiestructurados y, a continuación, consultará objetos de datos semiestructurados en diversos casos de uso.

nota

Le recomendamos que establezca las opciones de configuración enable_case_sensitive_identifier y enable_case_sensitive_super_attribute antes de trabajar con el tipo de datos SUPER. Para obtener más información, consulte enable_case_sensitive_identifier y enable_case_sensitive_super_attribute.

Carga de datos semiestructurados

En las instrucciones siguientes, se crea una tabla de ejemplo y se carga un objeto JSON de ejemplo en la columna SUPER all_data.


DROP TABLE IF EXISTS test_json;

SET enable_case_sensitive_super_attribute TO true;
SET enable_case_sensitive_identifier TO true;

CREATE TABLE test_json (all_data SUPER);

INSERT INTO test_json VALUES (JSON_PARSE('
{
   "data":{
      "pnr":{
         "type":"pnr",
         "pnrid":"123PQRS-2024-09-20",
         "bookingIdentifier":"123PQRS",
         "version":"5",
         "triggerType":"",
         "events":[
            {
               "eventType":"UPDATED",
               "type":"PART",
               "id":"123PQRS-2024-09-20-HO-1"
            },
            {
               "eventType":"CREATED",
               "type":"ABC",
               "id":"123PQRS-2024-09-20-OT-38"
            }
         ],
         "create":{
            "pnrCreateDate":"2024-09-20T16:56:00Z",
            "officeID":"OFFCID1234",
            "officeIDCategory":"Email"
         },
         "lastModification":{
            "dateTime":"2024-09-20T17:09:00Z"
         },
         "PARTDetails":[
            {
               "path":"1",
               "TrainPARTs":[
                  {
                     "PARTID":"123PQRS-2024-09-20-HO-1",
                     "departure":{
                        "departureStation":"XYZ",
                        "departureTimeLocal":"2024-10-03T06:30:00",
                        "departureTimeGMT":"2024-10-03T10:30:00Z"
                     },
                     "arrival":{
                        "arrivalStation":"ABC",
                        "arrivalTimeLocal":"2024-10-03T08:20:00",
                        "arrivalTimeGMT":"2024-10-03T15:20:00Z"
                     },
                     "marketing":{
                        "carrierCode":"XX",
                        "TrainNumber":"100"
                     },
                     "operating":{
                        "carrierCode":"YY",
                        "TrainNumber":"100-A"
                     },
                     "status":"ON",
                     "aircraft":{
                        "code":"222"
                     },
                     "class":"WC",
                     "first":"Y",
                     "seating":[
                        
                     ]
                  }
               ]
            }
         ],
         "commuterInformation":[
            {
               "commuterID":"2",
               "commuterPNR":"123PQRS-2024-09-20-RO-2",
               "commuterTypeCode":"DOM",
               "firstName":"JOHN",
               "lastName":"MILLER"
            }
         ],
         "contactDetail":[
            {
               "emailContacts":[
                  {
                     "id":"123PQRS-2024-09-20-OT-4",
                     "contact":"JOHNMILLER@EXAMPLE.COM",
                     "purpose":[
                        "BUSINESS"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":"EN"
                  },
                  {
                     "id":"123PQRS-2024-09-20-OT-5",
                     "contact":"HARVEYCORMIER@EXAMPLE.COM",
                     "purpose":[
                        "NOTIFICATION"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":"EN"
                  }
               ]
            },
            {
               "phoneContacts":[
                  {
                     "id":"123PQRS-2024-09-20-OT-3",
                     "contact":"1234567890",
                     "purpose":[
                        "NOTIFICATION"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ],
                     "language":""
                  }
               ]
            },
            {
               "addressInfo":[
                  {
                     "id":"123PQRS-2024-09-20-OT-6",
                     "addressline":[
                        "112 PORT STREET"
                     ],
                     "provinceState":"CA",
                     "cityName":"SAN JOSE",
                     "postalCode":"12345",
                     "countryCode":"USA",
                     "purpose":[
                        "MAILING"
                     ],
                     "commuter":[
                        "123PQRS-2024-09-20-RO-2"
                     ]
                  }
               ]
            }
         ],
         "PendingService":[
            {
               "id":"123PQRS-2024-09-20-OT-26",
               "code":"MONO",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ123 Seat-No 567",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-27",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ567 Seat-No 111",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-28",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ890 Seat-No 123",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            },
            {
               "id":"123PQRS-2024-09-20-OT-29",
               "code":"OTHS",
               "status":"",
               "text":"Broken Seat at Coach-No XYZ111 Seat-No 333",
               "trainCode":"WC-1",
               "TrainsArray":[
                  "123PQRS-2024-09-20-HO-1"
               ],
               "commuter":[
                  "123PQRS-2024-09-20-RO-2"
               ]
            }            
         ],
         "parts": [
            {
               "partname": "prop",
               "manufacturer": "local parts co",
               "quality": 2,
               "price": 10.00
            },
            {
               "partname": "prop",
               "manufacturer": "big parts co",
               "quality": null,
               "price": 9.00
            },
            {
               "partname": "prop",
               "manufacturer": "small parts co",
               "quality": 1,
               "price": 12.00
            },
            {
               "partname": "rudder",
               "manufacturer": "local parts co",
               "quality": 1,
               "price": 2.50
            },
            {
               "partname": "rudder",
               "manufacturer": "big parts co",
               "quality": 2,
               "price": 3.75
            },
            {
               "partname": "rudder",
               "manufacturer": "small parts co",
               "quality": null,
               "price": 1.90
            },
            {
               "partname": "wing",
               "manufacturer": "local parts co",
               "quality": null,
               "price": 7.50
            },
            {
               "partname": "wing",
               "manufacturer": "big parts co",
               "quality": 1,
               "price": 15.20
            },
            {
               "partname": "wing",
               "manufacturer": "small parts co",
               "quality": null,
               "price": 11.80
            }
         ],
         "count_by_color": [
            {
               "quality": "high",
               "red": 15,
               "green": 20,
               "blue": 7
            },
            {
               "quality": "normal",
               "red": 35,
               "green": null,
               "blue": 40
            },
            {
               "quality": "low",
               "red": 10,
               "green": 23,
               "blue": null
            }
         ]
       }
   },
   "id":"abcdefgh-ijklmnop-qrstuvwxyz123",
   "mainIds":[
      {
         "ID":"pqrstuvwxyz-aabbcc123",
         "Source":"NYC"
      }
   ]
}
'));

Consulta de datos semiestructurados anidados

En la instrucción siguiente, se utiliza la notación de puntos de PartiQL para extraer el campo pnrid, que está anidado a tres niveles de profundidad dentro del objeto all_data de nivel superior.


select all_data.data.pnr.pnrid::varchar from test_json;

 pnrid
--------------------
 123PQRS-2024-09-20

En la instrucción siguiente, se usa la notación entre corchetes de PartiQL para especificar y extraer solo el primer elemento de la matriz de events anidada dentro del objeto de nivel superior.


SELECT
    all_data.data.pnr.events[0]
FROM test_json;

 events
---------------------------------
{
   "eventType":"UPDATED",
   "type":"PART",
   "id":"123PQRS-2024-09-20-HO-1"
}

En la instrucción siguiente, se extrae solo la propiedad eventType del elemento especificado de la matriz de events.


SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;

 eventtype
-----------
 "UPDATED"

Las instrucciones siguientes

Uso de `enable_case_sensitive_identifier` y `enable_case_sensitive_super_attribute` con datos semiestructurados

Los ejemplos siguientes muestran en qué se diferencian las opciones de configuración enable_case_sensitive_identifier y enable_case_sensitive_super_attribute cuando se utilizan para consultar datos semiestructurados. Para obtener más información acerca de estas opciones de configuración, consulte Acceso a campos JSON con nombres de campo o atributos en mayúsculas y con mayúsculas y minúsculas combinadas.

En la instrucción siguiente, el restablecer ambas opciones de configuración al valor predeterminado de false hace que la consulta devuelva un valor NULL.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;

 eventtype
-----------
NULL

En el ejemplo siguiente, la consulta de ejemplo devuelve el resultado deseado después de escribir los atributos que distinguen mayúsculas de minúsculas entre comillas dobles y establecer enable_case_sensitive_identifier en true.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0]."eventType"
FROM test_json;
         
 eventtype
-----------
NULL

SET enable_case_sensitive_identifier TO true;
         
SELECT
    all_data.data.pnr.events[0]."eventType"
FROM test_json;
         
 eventtype
-----------
 "UPDATED"

En el ejemplo siguiente, la consulta de ejemplo devuelve el resultado deseado después de establecer enable_case_sensitive_super_attribute en true sin escribir los atributos que distinguen mayúsculas de minúsculas entre comillas dobles.


RESET enable_case_sensitive_identifier;
RESET enable_case_sensitive_super_attribute;

SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;
         
 eventtype
-----------
NULL
         
SET enable_case_sensitive_super_attribute TO true;
         
SELECT
    all_data.data.pnr.events[0].eventType
FROM test_json;
         
 eventtype
-----------
 "UPDATED"

Filtrado de datos semiestructurados

En la instrucción siguiente, se usa la sintaxis PartiQL en la cláusula WHERE de una instrucción que cuenta eventos del tipo UPDATED para recuperar datos de un atributo determinado del interior de una matriz. Puede usar esta sintaxis en cualquier parte de la consulta en la que normalmente haría referencia a las columnas.


SELECT COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType = 'UPDATED';

 count
------
 1

En el ejemplo siguiente, se utiliza la sintaxis con corchetes y puntos de PartiQL tanto en las cláusulas GROUP BY como ORDER BY.


SELECT all_data.data.pnr.events[0].eventType::varchar,
       COUNT(*)
FROM test_json
WHERE all_data.data.pnr.events[0].eventType IS NOT NULL
GROUP BY all_data.data.pnr.events[0].eventType
ORDER BY all_data.data.pnr.events[0].eventType;

 eventtype | count
-----------+-------
 "UPDATED" | 1

Desanidamiento de datos semiestructurados

En la instrucción siguiente, se usa uniones PartiQL para desanidar la matriz de events. Tenga en cuenta que esta unión funciona incluso cuando el número de índices de la matriz no es estático.

Para ver ejemplos de desanidamiento de datos semiestructurados mediante UNNEST en la cláusula FROM, consulte Ejemplos de UNNEST.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
b.eventType::varchar event_type,
b.id::varchar event_id
FROM test_json a, 
  a.all_data.data.pnr.events b;

 type_info | pnr_id              | booking_id | version_info | event_type | event_id
-----------+---------------------+------------+--------------+------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | UPDATED    | 123PQRS-2024-09-20-HO-1
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | CREATED    | 123PQRS-2024-09-20-OT-38

Desanidamiento de matrices anidadas

En la instrucción siguiente, se usa uniones PartiQL para deshacer una matriz que está anidada dentro de otra matriz.

Para ver ejemplos de desanidamiento de datos semiestructurados mediante UNNEST en la cláusula FROM, consulte Ejemplos de UNNEST.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact,
e::varchar email_purpose,
f::varchar email_commuter
FROM test_json a,
  a.all_data.data.pnr.contactDetail c,
  c."emailContacts" d,
  d.purpose e,
  d.commuter f;

 type_info | pnr_id              | booking_id | version_info | email_record_id         | email_contact             | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM    | BUSINESS      | 123PQRS-2024-09-20-RO-2
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-5 | HARVEYCORMIER@EXAMPLE.COM | NOTIFICATION  | 123PQRS-2024-09-20-RO-2

Uso de datos semiestructurados en subconsultas

En la instrucción siguiente, se utiliza una subconsulta de la cláusula WHERE para devolver solo una subsección de los resultados del ejemplo anterior.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
d.id::varchar email_record_id,
d.contact::varchar email_contact
FROM test_json a,
a.all_data.data.pnr.contactDetail c,
c."emailContacts" d
WHERE (SELECT COUNT(*) FROM d.purpose e WHERE e = 'BUSINESS') > 0;

 type_info | pnr_id              | booking_id | version_info | email_record_id         | email_contact             | email_purpose | email_commuter
-----------+---------------------+------------+--------------+-------------------------+---------------------------+---------------+-------------------------
 pnr       | 123PQRS-2024-09-20  | 123PQRS    | 5            | 123PQRS-2024-09-20-OT-4 | JOHNMILLER@EXAMPLE.COM    | BUSINESS      | 123PQRS-2024-09-20-RO-2

Agregación de consultas mediante datos semiestructurados

En la instrucción siguiente, se utiliza la función COUNT para agregar el número de elementos en la matriz de PendingService.


SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a, 
  a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;

 type_info | pnr_id             | booking_id | version_info | total_pending_service
-----------+--------------------+------------+--------------+-----------------------
 pnr       | 123PQRS-2024-09-20 | 123PQRS    | 5            | 4

Uso de datos semiestructurados en vistas materializadas

En la instrucción siguiente, se utiliza la instrucción del ejemplo anterior para crear una vista materializada. La vista materializada actualiza automáticamente el número de servicios pendientes cuando la tabla base obtiene nuevos datos.


CREATE MATERIALIZED VIEW mv_total_pending_service
AUTO REFRESH YES
AS
SELECT
a.all_data.data.pnr.type::varchar type_info,
a.all_data.data.pnr.pnrid::varchar pnr_id ,
a.all_data.data.pnr.bookingIdentifier::varchar booking_id,
a.all_data.data.pnr.version::varchar version_info,
COUNT(*) AS total_pending_service
FROM test_json a,
  a.all_data.data.pnr.PendingService c
GROUP BY 1,2,3,4;

Uso de PIVOT y UNPIVOT con datos semiestructurados

En la instrucción siguiente, se utiliza PIVOT en la columna partname para devolver el precio medio de cada pieza.


SELECT *
FROM 
(
SELECT
c.partname::varchar, c.price
FROM test_json a, 
  a.all_data.data.pnr.parts c) 
PIVOT (AVG(price) for partname IN ('prop', 'rudder', 'wing'));
            
 
 prop       | rudder             |  wing
------------+--------------------+--------
 10.33      | 2.71               |  11.50

En el ejemplo anterior, los resultados se transforman en columnas. En el ejemplo siguiente, se muestra una consulta de GROUP BY que devuelve los precios de media en filas, en lugar de hacerlo en columnas.


SELECT partname, avg(price)
FROM (
SELECT
c.partname::varchar, c.price
FROM test_json a, 
  a.all_data.data.pnr.parts c)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
            
 partname |  avg
----------+-------
 prop     | 10.33
 rudder   |  2.71
 wing     | 11.50

A continuación, se muestra un ejemplo de PIVOT con manufacturer como una columna implícita.


SELECT *
FROM (
SELECT
c.quality, c.manufacturer::varchar
FROM test_json a, 
  a.all_data.data.pnr.parts c) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
            
 manufacturer      | 1  | 2  | null
-------------------+----+----+------
 local parts co    | 1  | 1  |  1
 big parts co      | 1  | 1  |  1
 small parts co    | 1  | 0  |  2

A continuación, se muestra un ejemplo de UNPIVOT en la columna quality.


SELECT *
FROM 
(
SELECT
c.quality as quality
FROM test_json a, 
  a.all_data.data.pnr.parts c) 
UNPIVOT (cnt FOR column_header IN (quality));
            
 column_header   | cnt
-----------------+----
 quality         | 2    
 quality         | 1    
 quality         | 1    
 quality         | 2    
 quality         | 1

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Creación de columnas escalares de Amazon Redshift a partir de datos fragmentados

Machine learning