Serialización de datos JSON anidados y complejos

En este tema se muestra cómo serializar datos anidados en formato JSON. Los datos anidados son datos que contienen campos anidados. Los campos anidados son campos que se unen como una sola entidad, por ejemplo, matrices, estructuras u objetos.

Una alternativa a los métodos demostrados en este tutorial es consultar columnas de recopilación anidada de nivel superior como datos JSON serializados. Puede utilizar la serialización para inspeccionar, convertir y capturar datos anidados como JSON con Redshift Spectrum. Este método es compatible con los formatos ORC, JSON, Ion y Parquet. Utilice el parámetro de configuración de sesión json_serialization_enable para establecer el comportamiento de serialización. Una vez que se establece, los tipos de datos JSON complejos se serializan en VARCHAR(65535). Se puede acceder a los datos JSON anidados con Funciones JSON. Para obtener más información, consulte json_serialization_enable.

Por ejemplo, sin establecer json_serialization_enable, las siguientes consultas que acceden directamente a columnas anidadas producen error.

SELECT * FROM spectrum.customers LIMIT 1;

=> ERROR:  Nested tables do not support '*' in the SELECT clause.

SELECT name FROM spectrum.customers LIMIT 1;

=> ERROR:  column "name" does not exist in customers

La configuración de json_serialization_enable permite consultar directamente las recopilaciones de nivel superior.

SET json_serialization_enable TO true;

SELECT * FROM spectrum.customers order by id LIMIT 1;

id | name                                 | phones         | orders
---+--------------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------
1  | {"given": "John", "family": "Smith"} | ["123-457789"] | [{"shipdate": "2018-03-01T11:59:59.000Z", "price": 100.50}, {"shipdate": "2018-03-01T09:10:00.000Z", "price": 99.12}]          
 
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": "John", "family": "Smith"}  

Tenga en cuenta los siguientes elementos a la hora de serializar los datos JSON anidados.

• Cuando las columnas de recopilación se serializan como VARCHAR(65535), no se puede acceder directamente a sus subcampos anidados como parte de la sintaxis de la consulta (por ejemplo, en la cláusula de filtro). Sin embargo, las funciones JSON se pueden usar para acceder a datos JSON anidados.

• No se admiten las siguientes representaciones especializadas:

  • uniones ORC

  • mapas ORC con claves de tipo complejo

  • datagramas Ion

  • Ion SEXP

• Las marcas temporales se devuelven como cadenas serializadas ISO.

• Las claves de mapa primitivas se promueven a la categoría de cadena (por ejemplo, de 1 a "1").

• Los valores nulos de nivel superior se serializan como NULL.

• Si la serialización supera el tamaño máximo de VARCHAR de 65535, la celda se establece como NULL.

Serialización de tipos complejos que contienen cadenas JSON

De manera predeterminada, los valores de cadena contenidos en recopilaciones anidadas se serializan como cadenas JSON con escape. El escape puede ser no deseado cuando las cadenas son datos JSON válidos. En cambio, es posible que desee escribir directamente como JSON subelementos anidados o campos VARCHAR. Habilite este comportamiento con la configuración de json_serialization_parse_nested_strings a nivel de la sesión. Cuando se establecen json_serialization_enable y json_serialization_parse_nested_strings, los valores JSON válidos se serializan en línea sin caracteres de escape. Cuando el valor no tenga el formato JSON válido, se escapa como si el valor de configuración json_serialization_parse_nested_strings no se hubiera establecido. Para obtener más información, consulte json_serialization_parse_nested_strings.

Por ejemplo, suponga que los datos del ejemplo anterior contenían JSON como un tipo complejo de structs en el campo name VARCHAR(20):

name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}

Cuando se haya establecido json_serialization_parse_nested_strings, la columna name se serializa de la siguiente manera:

SET json_serialization_enable TO true;
SET json_serialization_parse_nested_strings TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": {"first":"John","middle":"James"}, "family": "Smith"}

En lugar del siguiente escape:

SET json_serialization_enable TO true;
SELECT name FROM spectrum.customers order by id LIMIT 1;

name
---------
{"given": "{\"first\":\"John\",\"middle\":\"James\"}", "family": "Smith"}