Serializar JSON aninhado complexo

Uma alternativa aos métodos demonstrados neste tutorial é consultar colunas de coleção aninhadas de nível superior como JSON serializado. Você pode usar a serialização para inspecionar, converter e ingerir dados aninhados como JSON com Redshift Spectrum. Este método é compatível com os formatos ORC, JSON, Ion e Parquet. Usar o parâmetro de configuração da sessão json_serialization_enable para configurar o comportamento de serialização. Quando definido, tipos de dados JSON complexos são serializados para VARCHAR (65535). O JSON aninhado pode ser acessado com Funções JSON. Para obter mais informações, consulte json_serialization_enable.

Por exemplo, sem definir json_serialization_enable, as consultas a seguir que acessam colunas aninhadas falham diretamente.

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

Configurar json_serialization_enable permite consultar coleções de nível superior diretamente.

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

Considere os seguintes itens ao serializar JSON aninhado.

• Quando colunas de coleção são serializadas como VARCHAR (65535), seus subcampos aninhados não podem ser acessados diretamente como parte da sintaxe de consulta (por exemplo, na cláusula de filtro). No entanto, as funções JSON podem ser usadas para acessar JSON aninhado.

• As seguintes representações especializadas não são compatíveis:

  • Junções ORC

  • Mapas ORC com chaves de tipo complexas

  • Datagramas Ion

  • SEXP Ion

• Os timestamps são retornados como strings serializadas ISO.

• Chaves de mapa primitivas são promovidas para string (por exemplo, 1 para "1").

• Valores nulos de nível superior são serializados como NULLs.

• Se a serialização ultrapassa o tamanho máximo VARCHAR de 65535, a célula é definida como NULL.

Serializar tipos complexos contendo strings JSON

Por padrão, os valores de string contidos em coleções aninhadas são serializados como strings JSON escapadas. Escapar pode ser indesejável quando as strings são JSON válidos. Em vez disso, você pode querer gravar subelementos aninhados ou campos que são VARCHAR diretamente como JSON. Habilite esse comportamento com a configuração no nível da sessão json_serialization_parse_nested_strings. Quando ambos json_serialization_enable e json_serialization_parse_nested_strings são definidos, valores JSON válidos são serializados inline sem caracteres de escape. Quando o valor não é um JSON válido, ele é escapado como se a propriedade json_serialization_parse_nested_strings não foi definido. Para obter mais informações, consulte json_serialization_parse_nested_strings.

Por exemplo, suponha que os dados do exemplo anterior continha JSON como um tipo complexo structs no campo nameVARCHAR(20):

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

Quando json_serialization_parse_nested_strings estiver definido, a propriedade name é serializada da seguinte forma:

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

Em vez de ser escapado assim:

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

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