o que é o tipo de dados json, como usá-lo - MaxCompute: Central de Documentação do Alibaba Cloud

Quando usar o tipo JSON

Cenário	Recomendação
Dados JSON semiestruturados com schema variável entre linhas	Use o tipo JSON
Schema totalmente fixo, com todos os campos sempre presentes	Prefira colunas tipadas para maior segurança de tipos e ampla compatibilidade com SQL
Necessidade de usar `ORDER BY`, `GROUP BY` ou `JOIN` na coluna JSON	Opte por colunas tipadas, pois essas operações não têm suporte em colunas JSON

Funcionamento

Ao inserir dados JSON, o MaxCompute extrai automaticamente um schema público dos dados e aplica otimizações. Os campos presentes no schema público são armazenados em formato colunar, enquanto os ausentes são guardados como BINARY.

Por exemplo, considere três linhas contendo os campos a, b e c:

INSERT INTO json_table
SELECT json_parse(string_val)
FROM   string_table;

O MaxCompute extrai o schema público <"a":binary, "b":bigint, "c":bigint>. Uma consulta subsequente que leia apenas b e c varrerá somente essas colunas:

SELECT json_val["b"], json_val["c"]
FROM   json_table;
-- Column pruning keeps only b and c.
+------+------+
| _c0  | _c1  |
+------+------+
| 2    | NULL |
| 2    | NULL |
| NULL | 3    |
+------+------+

Os valores JSON utilizam tipos internos do MaxCompute para armazenamento. Devido a esse mapeamento, valores fora dos intervalos BIGINT ou DOUBLE podem causar overflow ou perda de precisão.

Pré-requisitos

Antes de começar, certifique-se de ter:

Um projeto MaxCompute com o tipo JSON ativado (consulte Ativar o tipo JSON)
Java SDK V0.44.0 ou posterior, ou PyODPS V0.11.4.1 ou posterior
Caso utilize odpscmd: versão V0.46.5 ou posterior, com use_instance_tunnel=false definido em conf\odps_config.ini

Ativar o tipo JSON

A flag odps.sql.type.json.enable controla a disponibilidade do tipo JSON:

Tipo de projeto	Valor padrão
Novos projetos	`true`
Projetos existentes	`false`

Para ativar o tipo JSON em um projeto existente:

SET odps.sql.type.json.enable=true;

Para verificar o valor atual:

setproject;

Limitações

Referência de restrições

Categoria	Restrição	Implicação
Operações de tabela	Não é possível adicionar uma coluna JSON a uma tabela existente	Crie uma nova tabela e migre os dados com `INSERT INTO ... SELECT`
Tipos de tabela	Tabelas clusterizadas e o tipo Delta Table não têm suporte	Utilize tabelas padrão
Operações SQL	Chaves de `ORDER BY`, `GROUP BY` e `JOIN` em colunas JSON não têm suporte	Extraia o valor para uma coluna tipada previamente
Operações SQL	Operações de comparação no tipo JSON não têm suporte	Faça cast para STRING ou para uma coluna tipada antes de comparar
Aninhamento	Profundidade máxima de 20 níveis	Achate estruturas profundamente aninhadas antes da ingestão
Compatibilidade de engine	O Hologres não lê colunas JSON	Mantenha uma cópia em STRING se forem necessárias leituras entre engines
Funções definidas pelo usuário (UDFs)	UDFs Java e Python não aceitam o tipo JSON	Utilize funções JSON nativas como alternativa
Ferramentas	Dataphin e outros ecossistemas externos não têm suporte	Verifique a compatibilidade antes do uso

Armazenamento de tipos e precisão

Os valores JSON são mapeados para tipos internos do MaxCompute. Valores fora desses intervalos causam overflow ou perda de precisão:

Tipo JSON	Armazenamento interno	Observação
NUMBER (parte inteira)	BIGINT	Overflow se estiver fora do intervalo BIGINT
NUMBER (parte decimal)	DOUBLE	Possível perda de precisão
STRING	BINARY (não público) ou coluna tipada	`\u0000` não tem suporte
BOOLEAN	BOOLEAN
NULL	—	`json 'null'` difere do `NULL` SQL
ARRAY	ARRAY
OBJECT	OBJECT

Requisitos de ferramentas e SDK

Ferramenta ou SDK	Requisito
odpscmd (cliente MaxCompute)	V0.46.5 ou posterior; defina `use_instance_tunnel=false` em `conf\odps_config.ini`
MaxCompute Studio	Compatível
DataWorks	Compatível
Java SDK	V0.44.0 ou posterior
PyODPS	V0.11.4.1 ou posterior

Trabalhar com o tipo JSON

Todos os exemplos abaixo utilizam um registro de pedido compartilhado para demonstrar padrões de criação, inserção e consulta de ponta a ponta:

{"id": 1001, "customer": "Molly", "amount": 299.50}

Criar uma tabela JSON

Não é necessária definição de schema — declare a coluna como JSON:

CREATE TABLE orders (record JSON);

Gerar dados JSON

A partir de um literal JSON:

INSERT INTO orders VALUES (JSON '{"id": 1001, "customer": "Molly", "amount": 299.50}');

Usando JSON_OBJECT e JSON_ARRAY:

-- JSON_OBJECT builds a JSON object from key-value pairs.
INSERT INTO orders SELECT JSON_OBJECT("id", 1002, "customer", "Frank", "amount", 150.00);

-- JSON_ARRAY builds a JSON array.
SELECT JSON_ARRAY("tag1", "tag2", "promo");
-- Returns: ["tag1","tag2","promo"]

Convertendo uma coluna STRING:

Use json_parse para converter dados string existentes. Envolva com json_valid para ignorar linhas malformadas:

INSERT INTO orders
SELECT json_parse(raw_json)
FROM   staging_table
WHERE  json_valid(raw_json);

CAST("abc" AS JSON) e json_parse("abc") comportam-se de maneira diferente em casos extremos. Consulte Funções JSON para detalhes.

Acessar dados JSON

Todos os exemplos abaixo consultam a linha inserida anteriormente: {"id": 1001, "customer": "Molly", "amount": 299.50}.

Acesso por índice

O acesso por índice utiliza modo estrito — retorna NULL quando o caminho não corresponde à estrutura dos dados.

-- Returns 1001
SELECT record['id']
FROM   orders
WHERE  record['id'] IS NOT NULL;

-- Returns "Molly"
SELECT record['customer']
FROM   orders;

-- Returns NULL (field does not exist)
SELECT record['email']
FROM   orders;

Esse acesso equivale a JSON_EXTRACT em modo estrito:

-- These two expressions return the same result:
SELECT record['id']                          FROM orders;
SELECT JSON_EXTRACT(record, 'strict $.id')   FROM orders;
-- Both return: 1001

Acesso usando funções JSON

Duas funções estão disponíveis:

Função	Retorno	Parser de JSON Path
`JSON_EXTRACT`	Tipo JSON	Novo parser padronizado (subconjunto compatível com PostgreSQL)
`GET_JSON_OBJECT`	Tipo STRING	Parser legado

Prefira JSON_EXTRACT em novos códigos SQL, pois seu parser é consistente com o acessador por índice e suporta column pruning em modo estrito.

-- JSON_EXTRACT returns a JSON value (with quotes).
SELECT JSON_EXTRACT(record, '$.customer')
FROM   orders;
-- Returns: "Molly"

-- GET_JSON_OBJECT returns a STRING value (no quotes).
SELECT GET_JSON_OBJECT(record, '$.customer')
FROM   orders;
-- Returns: Molly

Referência de JSON Path

Um JSON Path identifica um nó nos dados JSON. O parser utilizado pelo tipo JSON é um subconjunto da especificação JSON Path do PostgreSQL.

Dados de amostra para os exemplos de JSON Path:

{
  "name": "Molly",
  "phones": [
    { "phonetype": "work",  "phone#": "650-506-7000" },
    { "phonetype": "cell",  "phone#": "650-555-5555" }
  ]
}

Sintaxe de acessadores:

Acessador	Exemplo	Descrição
Membro	`$.name`	Acessa um campo pelo nome
Membro (caracteres especiais)	`$."phone#"`	Usa aspas para nomes com caracteres especiais
Membro curinga	`$.*`	Todos os campos de um objeto
Elemento	`$.phones[1]`	Elemento de array por índice
Intervalo de elementos	`$.phones[0, 1]` ou `$[1, 2, 4 to 7]`	Elementos em índices específicos ou em um intervalo
Elemento curinga	`$.phones[*]`	Todos os elementos do array

Modos:

O JSON Path suporta dois modos. O padrão é lax.

Modo	Comportamento	Column pruning
`lax`	Envolve escalares como arrays e desenrola arrays para objetos automaticamente quando o caminho espera uma estrutura diferente. Retorna resultados de forma permissiva.	Sem suporte
`strict`	Retorna NULL se o caminho não corresponder exatamente à estrutura real dos dados.	Com suporte

Exemplos em modo lax (usando os dados de amostra acima):

Expressão	Resultado	Motivo
`lax $.phones.phonetype`	`["work","cell"]`	Desenrola o array phones e lê phonetype de cada objeto
`lax $.phones[*].phonetype`	`["work","cell"]`	Acesso direto a elemento curinga
`lax $.name[*]`	`["Molly"]`	Envolve a string "Molly" em um array
`lax $.name.*`	`NULL`	Espera um objeto sob name, mas encontra uma string

Exemplos em modo strict:

Expressão	Resultado	Motivo
`strict $.phones[1]."phone#"`	`"650-555-5555"`	Correspondência exata do caminho
`strict $.phones.phonetype`	`NULL`	phones é um array; esperava-se um objeto
`strict $.address`	`NULL`	Campo inexistente

Importante

Use o modo strict quando precisar de column pruning. O modo lax não oferece suporte à otimização de column pruning.

Considerações de design

Adote o modo strict para column pruning. O modo lax não aciona essa otimização, fazendo com que as consultas varram mais dados.
Mantenha documentos JSON pequenos. Cada coluna JSON é armazenada como um único valor de coluna. Documentos grandes aumentam o custo de memória e I/O por linha.
Valide antes da ingestão. Use json_valid() para filtrar linhas malformadas antes de chamar json_parse().
Verifique a precisão antes de inserir números grandes. Números JSON são armazenados como BIGINT (parte inteira) e DOUBLE (parte decimal). Valores fora desses intervalos causam overflow.
Planeje o schema antes de criar tabelas. Não é possível adicionar uma coluna JSON a uma tabela existente. Projete a tabela com a coluna JSON desde o início.

Exemplo completo

-- Enable the JSON type if your project was created before the feature was enabled.
SET odps.sql.type.json.enable=true;

-- Create a JSON table.
CREATE TABLE orders (record JSON);

-- Ingest from a staging STRING table, skipping malformed rows.
CREATE TABLE staging (raw_json STRING);
INSERT INTO staging VALUES ('{"id": 1001, "customer": "Molly", "amount": 299.50}');

INSERT INTO orders
SELECT json_parse(raw_json)
FROM   staging
WHERE  json_valid(raw_json);

-- Query all non-null records.
SELECT * FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +--------------------------------------------------+
-- | record                                           |
-- +--------------------------------------------------+
-- | {"id":1001,"customer":"Molly","amount":299.5}    |
-- +--------------------------------------------------+

-- Access a specific field.
SELECT record['customer'] FROM orders WHERE record IS NOT NULL;
-- Returns:
-- +-----------+
-- | _c0       |
-- +-----------+
-- | "Molly"   |
-- +-----------+

Próximos passos

Funções JSON — referência completa para JSON_EXTRACT, GET_JSON_OBJECT, JSON_OBJECT, JSON_ARRAY, json_parse, json_valid e funções relacionadas
Expressões CAST — comportamento de conversão de tipos para JSON