Funções JSON - Lindorm: Central de Documentação do Alibaba Cloud

Visão geral das funções JSON

Classificação da função	Nome da função	Descrição da função
Funções construtoras de JSON	json_object	Cria um objeto JSON.
Funções construtoras de JSON	json_array	Cria um array JSON.
Funções de extração de JSON	json_extract	Extrai um valor de um documento JSON no caminho especificado.
	json_extract_string	Extrai um valor no caminho especificado e o converte em string.
	json_extract_long	Extrai um valor no caminho especificado e o converte em inteiro longo.
	json_extract_double	Extrai um valor no caminho especificado e o converte em número de ponto flutuante.
Funções de verificação de contenção de JSON	json_contains	Verifica se um documento JSON contém todos os valores ou objetos especificados.
Funções de verificação de contenção de JSON	json_contains_any	Verifica se um documento JSON contém qualquer um dos valores ou objetos especificados.
Funções de atualização de JSON	json_set	Insere ou atualiza dados em um documento JSON e retorna o novo documento. Equivale a `json_insert` + `json_replace`.
	json_insert	Insere dados em um documento JSON e retorna o novo documento. Insere um novo campo apenas se ele não existir.
	json_replace	Substitui dados existentes em um documento JSON e retorna o novo documento. Atualiza o valor apenas se o campo existir.
	json_remove	Exclui dados de um documento JSON no caminho especificado e retorna o documento modificado.
	json_upsert	Semanticamente igual a `json_set`, mas capaz de lidar com valores NULL.

Descrições das funções

Funções construtoras de JSON

json_object

Cria um objeto JSON.

Sintaxe:

json_object(key1, value1, key2, value2, ...)

Parâmetros:

key: Nome da chave. Deve ser uma string.
value: Valor. Pode ser de qualquer tipo.

Exemplos:

-- Create a simple object
SELECT json_object('name', 'Alice', 'age', 25);
-- Result: {"name": "Alice", "age": 25}

-- Use in an INSERT statement
UPSERT INTO users (id, data) VALUES (1, json_object('name', 'Charlie', 'city', 'Beijing'));

json_array

Cria um array JSON.

Sintaxe:

json_array(value1, value2, value3, ...)

Parâmetros:

value: Valor. Pode ser um escalar, objeto ou array.

Exemplos:

-- Create a simple array
SELECT json_array('apple', 'banana', 'orange');
-- Result: ["apple", "banana", "orange"]

-- Use in an INSERT statement
UPSERT INTO products (id, tags) VALUES (1, json_array('electronics', 'mobile', 'smartphone'));

Funções de extração de JSON

json_extract

Extrai um valor de um documento JSON no caminho especificado.

Sintaxe:

json_extract(json_column, 'path')

Parâmetros:

json_column: Coluna ou expressão do tipo JSON.
path: Expressão de caminho JSON, como $.field, $.array[0] ou $.nested.field.

Exemplos:

-- Extract a simple field
SELECT json_extract('{"name": "Alice", "age": 25}', '$.name');
-- Result: "Alice"

-- Extract a nested field
SELECT json_extract('{"user": {"name": "Bob", "age": 30}}', '$.user.name');
-- Result: "Bob"

-- Extract an array element
SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills[0]');
-- Result: "Java"

-- Extract the entire array
SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills');
-- Result: ["Java", "Python"]

-- WHERE clause with a number
select * from tb where json_extract(c2, '$.k3.k4') > 5;

-- WHERE clause with a string
select * from tb where json_extract_string(c2, '$.k2') = '1';

Funções de extração com segurança de tipos: json_extract_type

json_extract_string

Extrai um valor no caminho especificado e o converte em string. Se o valor no caminho não for uma string, retorna NULL por padrão.

SELECT json_extract_string('{"name": "Alice"}', '$.name');
-- Result: Alice (VARCHAR type)

SELECT json_extract_string('{"number": "30"}', '$.name');
-- Result: NULL. (The type is Number, which does not match.)

json_extract_long

Extrai um valor no caminho especificado e o converte em inteiro longo. Se o valor no caminho não for um número, o Java Database Connectivity (JDBC) gera um erro.

SELECT json_extract_long('{"id": 123456789}', '$.id');
-- Result: 123456789 (LONG type)

SELECT json_extract_long('{"id": 123456.789}', '$.id');
-- Throws an error

SELECT json_extract_long('{"id": "123456.789"}', '$.id');
-- Throws an error

json_extract_double

Extrai um valor no caminho especificado e o converte em número de ponto flutuante. Se o valor no caminho não for um número de ponto flutuante, o JDBC gera um erro.

SELECT json_extract_double('{"id": 12345.56}', '$.id');
-- Result: 12345.56 (Double type)

SELECT json_extract_double('{"id": 12345}', '$.id');
-- Result: 12345.0 (Double type)

SELECT json_extract_double('{"id": "123456.789"}', '$.id');
-- Throws an error

Essas três funções possuem tipagem forte. O elemento no caminho especificado deve corresponder estritamente ao tipo da função. Caso contrário, a função retorna NULL ou gera um erro. É possível configurar o cluster para forçar a conversão de tipos, mas essa prática não é recomendada. Defina os tipos de dados antes de inserir dados JSON, pois tipos mistos podem causar problemas lógicos inesperados.

Funções de verificação de contenção de JSON

json_contains

Verifica se um documento JSON contém todos os valores ou objetos especificados.

Sintaxe:

json_contains(target, candidate[, path])

Parâmetros:

target_json: Obrigatório. Documento JSON.
candidate_json: Obrigatório. Documento JSON a ser verificado.
path: Opcional. Expressão de caminho.

Para obter mais informações sobre os parâmetros, consulte Additional parameter information.

Valor retornado:

1 (verdadeiro): Valor especificado encontrado.
0 (falso): O alvo não contém o candidato.

A função json_contains retorna 1 se o documento target_json contiver o documento candidate_json, e 0 caso contrário. Se você especificar o parâmetro path, a função verifica se os dados no path especificado contêm o documento candidate_json.

A função json_contains retorna NULL nos seguintes casos:

O caminho especificado não existe no documento JSON.
Qualquer parâmetro é NULL.

Exemplos:

-- Create a table and insert data
CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, data) VALUES (1, NULL);
UPSERT INTO test_table (id, data) VALUES (2, '{"skills": ["Java", "Python"]}');
UPSERT INTO test_table (id, data) VALUES (3, '{"skills": ["Go", "C"]}');
UPSERT INTO test_table (id, data) VALUES (4, '{"technical_skills": ["Java", "Go", "Rust"]}');
UPSERT INTO test_table (id, data) VALUES (5, '["Java","C++", "JavaScript"]');
UPSERT INTO test_table (id, data) VALUES (6, '{"skills": "Java"}');
UPSERT INTO test_table (id, data) VALUES (7, '["Java", "C#"]');
UPSERT INTO test_table (id, data) VALUES (8, '{"skills": ["Go", "Rust"]}');

-- Two-parameter form. The default path is '$'.
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]') and id>0 and id<10;
+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+
2 rows in set (0.01 sec)

-- Three-parameter form: Check a specified path
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+
1 row in set (0.01 sec)

-- Contains both "Java" and "Go"
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java","Go"]', '$.skills') and id>0 and id<10;
Empty set (0.02 sec)

-- The array or element contains the scalar "Java" 
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '"Java"', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    6 | {"skills": "Java"}             |
+------+--------------------------------+
2 rows in set (0.01 sec)

-- The array element contains an array with only the scalar "Java". (For id=6, the JSON document at the path is a scalar. A subset of a scalar can only be a scalar, not an array.)
mysql>  SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') and id>0 and id<10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+
1 row in set (0.01 sec)

-- Check if the entire document contains an object
SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');
-- Result: 1

-- Check if a specified path contains a value
SELECT json_contains('{"a": 1, "b": 2}', '1', '$.a');
-- Result: 1

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.b');
-- Result: 0

-- Check for containment in an array
SELECT json_contains('{"skills": ["Java", "Python"]}', '"Java"', '$.skills');
-- Result: 1

Informações adicionais sobre parâmetros

candidate_json é um documento JSON. Represente o elemento correspondente como uma string JSON.
- Documento JSON que contém o número 10: '10'
- Documento JSON que contém a string "10": '"10"'
- Documento JSON que contém uma lista de números: '[1,2,3]'
- Documento JSON que contém uma lista de strings: '["10","abc","key"]'
Um subconjunto de um escalar só pode ser um escalar. Um subconjunto de um array pode ser um elemento ou um array.

Classificações de tipos JSON:
- Escalar: String, Número (Inteiro/Double), Booleano, Nulo
- Tipo complexo: Array, Objeto
Considere a condição de verificação json_contains_any(skills, '["Java"]', '$.technical_skills'). Para o JSON no caminho $.technical_skills:
- Se o valor for {"technical_skills":"Java"}, a função retorna false. Um subconjunto de uma string não pode ser uma lista.
- Se o valor for {"technical_skills":["Java"]} ou {"technical_skills":["Java","Go"]}, a função retorna true.
Considere a condição de verificação json_contains_any(skills, '"Java"', '$.technical_skills') . Para o JSON no caminho $.technical_skills:
- Se o valor for {"technical_skills":"Java"}, a função retorna true.
- Se o valor for {"technical_skills":["Java"]} ou {"technical_skills":["Java","Go"]}, a função retorna true.

json_contains_any

Verifica se um documento JSON contém qualquer um dos valores ou objetos especificados.

Sintaxe:

A sintaxe é a mesma de json_contains. Para obter mais informações sobre as diferenças, consulte Comparison of JSON containment check functions.

Exemplos:

mysql> select json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
+--------+
| EXPR$0 |
+--------+
|      0 |
+--------+
1 row in set (0.02 sec)

mysql> select json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]','$.skills');
+--------+
| EXPR$0 |
+--------+
|      1 |
+--------+
1 row in set (0.03 sec)

-- Default two-parameter form. The path defaults to '$'.
mysql> SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go"]') limit 10;
+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+
2 rows in set (0.01 sec)

-- Three-parameter form
mysql>  SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go", "Rust"]', '$.skills') limit 10;
+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    3 | {"skills": ["Go", "C"]}        |
|    6 | {"skills": "Java"}             |
|    8 | {"skills": ["Go", "Rust"]}     |
+------+--------------------------------+
4 rows in set (0.01 sec)

-- Check if it contains any value from the array
SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
-- Result: 0
SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]','$.skills');
-- Result: 1

-- Check at a specified path
SELECT json_contains_any('{"departments": ["Engineering", "Sales"]}', '["Marketing", "Engineering"]', '$.departments');
-- Result: 1

Funções de atualização de JSON

json_set

Insere ou atualiza dados em um documento JSON e retorna o documento modificado. Esta função equivale a json_insert + json_replace.

Sintaxe:

json_set(json_column, 'path', new_value)

Comportamento:

Se o caminho especificado existir, esta função atualiza o valor nesse caminho.
Se o caminho especificado não existir, esta função insere o valor nesse caminho.
Se a coluna JSON for NULL, o resultado também será NULL. Nenhuma ação é executada.

Exemplos:

-- Update an existing field
UPSERT INTO test_table (id, data) VALUES (3, '{"name": "Charlie", "age": 30}');
UPDATE test_table SET data = json_set(data, '$.age', 31) WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31}

-- Insert a new field
UPDATE test_table SET data = json_set(data, '$.department', 'Engineering') WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31, "department": "Engineering"}

-- When the JSON column is NULL (key difference from json_upsert)
UPSERT INTO test_table (id,c1) VALUES (4,'test');
UPDATE test_table SET data = json_set(data, '$.name', 'David') WHERE id = 4;
-- Result: NULL

json_insert

Insere dados em um documento JSON e retorna o documento modificado. Esta função insere um novo valor apenas se o valor não existir no caminho especificado.

Sintaxe:

json_insert(json_column, 'path', new_value)

Comportamento:

Se o caminho especificado não existir, esta função insere o valor nesse caminho.
Se o caminho especificado já existir, esta função não executa nenhuma ação.

Exemplos:

-- Insert a new field
UPSERT INTO test_table (id, data) VALUES (5, '{"name": "Eve"}');
UPDATE test_table SET data = json_insert(data, '$.age', 28) WHERE id = 5;
-- Result: {"name": "Eve", "age": 28}

-- Attempt to insert an existing field (no effect)
UPDATE test_table SET data = json_insert(data, '$.name', 'New Name') WHERE id = 5;
-- Result: {"name": "Eve", "age": 28} (The name field remains unchanged)

json_replace

Substitui dados existentes em um documento JSON e retorna o documento modificado. Esta função atualiza um valor apenas se ele existir no caminho especificado.

Sintaxe:

json_replace(json_column, 'path', new_value)

Comportamento:

Se o caminho especificado existir, esta função atualiza o valor nesse caminho.
Se o caminho especificado não existir, esta função não executa nenhuma ação.

Exemplos:

-- Update an existing field
UPSERT INTO test_table (id, data) VALUES (6, '{"name": "Frank", "age": 35}');
UPDATE test_table SET data = json_replace(data, '$.age', 36) WHERE id = 6;
-- Result: {"name": "Frank", "age": 36}

-- Attempt to update a non-existent field (no effect)
UPDATE test_table SET data = json_replace(data, '$.city', 'Shanghai') WHERE id = 6;
-- Result: {"name": "Frank", "age": 36} (No city field)

json_remove

Exclui dados de um documento JSON no caminho especificado e retorna o documento modificado.

Sintaxe:

json_remove(json_column, 'path')

Comportamento:

Se o caminho especificado existir, esta função exclui os dados nesse caminho.
Se o caminho especificado não existir, esta é uma operação segura e nenhum erro ocorre.

Exemplos:

-- Delete a field
UPSERT INTO test_table (id, data) VALUES (7, '{"name": "Grace", "temp_field": "to_remove"}');
UPDATE test_table SET data = json_remove(data, '$.temp_field') WHERE id = 7;
-- Result: {"name": "Grace"}

-- Delete a non-existent field (safe)
UPDATE test_table SET data = json_remove(data, '$.nonexistent') WHERE id = 7;
-- Result: {"name": "Grace"} (No error)

json_upsert

Esta função é semanticamente igual a json_set, mas consegue lidar com valores NULL.

Sintaxe:

json_upsert(json_column, 'path', new_value)

Comportamento:

Se a coluna JSON for NULL, esta função cria um novo objeto: {path: new_value}
Se o caminho especificado existir, esta função atualiza o valor nesse caminho.
Se o caminho especificado não existir, esta função insere o valor nesse caminho.

Exemplos:

-- When the JSON column is NULL
CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id,c1) VALUES (1,'test');
UPDATE test_table SET data = json_upsert(data, '$.name', 'Alice') WHERE id = 1;
-- Result: {"name": "Alice"}

-- Update an existing field
UPSERT INTO test_table (id, data) VALUES (2, '{"name": "Bob", "age": 25}');
UPDATE test_table SET data = json_upsert(data, '$.age', 26) WHERE id = 2;
-- Result: {"name": "Bob", "age": 26}

-- Insert a new field
UPDATE test_table SET data = json_upsert(data, '$.city', 'Beijing') WHERE id = 2;
-- Result: {"name": "Bob", "age": 26, "city": "Beijing"}

Para obter mais informações sobre as diferenças entre essas funções de atualização de JSON, consulte Comparison of JSON update functions.

Resumo comparativo de funções

Comparação de funções de verificação de contenção de JSON

Função

Diferença

json_contains

Para json_contains, cada elemento em candidate_json também deve ser um elemento em target_json.

Em outras palavras, candidate_json deve ser um subconjunto de target_json.

json_contains_any

Para json_contains_any, pelo menos um elemento em candidate_json também deve ser um elemento em target_json.

Em outras palavras, candidate_json e target_json devem ter pelo menos um elemento em comum.

Comparação de funções de atualização de JSON

Função

Tratamento de coluna NULL

Campo existe

Campo não existe

Caso de uso principal

json_set

Permanece NULL

Atualização

Inserção

Atualiza uma coluna sabidamente não NULL. (Alinhado à semântica do MySQL).

json_upsert

Cria um novo objeto

Atualização

Inserção

Atualizações de uso geral. Insere mesmo se a coluna for NULL.

json_insert

Permanece NULL

Nenhuma operação

Inserção

Insere apenas novos campos.

json_replace

Permanece NULL

Atualização

Nenhuma operação

Atualiza apenas campos existentes.

json_remove

Permanece NULL

Exclusão

Nenhuma operação

Exclui campos.

Suporte a índices baseados em funções

Sintaxe

create_index_statement ::=  CREATE INDEX [ index_name ]
                                ON table_name '(' index_identifier ')'
                              [INCLUDE include_identifier]
                              [ASYNC]
                                 [ index_options ]
index_identifier       ::=  '('json_extract_type(column, json_path)')'
include_identifier   ::= '('column_name1,...,column_namen ')'

Descrições dos parâmetros

Parâmetro	Descrição
index_name	Nome da tabela de índice.
table_name	Nome da wide table.
json_extract_type	Extrai um campo de um tipo de dados específico de uma coluna JSON para usar como índice secundário. Se o tipo de dados não corresponder, o índice secundário não será criado. Os seguintes tipos de função são suportados: json_extract_string json_extract_long json_extract_double
column	Nome da coluna JSON.
json_path	Caminho na coluna JSON. Usado para extrair o valor no caminho especificado.
ASYNC	Cria o índice de forma assíncrona. Se você não adicionar ASYNC, o índice será criado de forma síncrona.

Para obter mais informações sobre o suporte a índices de pesquisa para funções JSON, consulte Use search indexes for the Lindorm JSON type.

Funções suportadas

As seguintes funções JSON suportam a criação de índices baseados em funções:

Acelerar consultas relacionadas a json_extract e json_extract_type

json_extract_string(json_column, 'path')
json_extract_long(json_column, 'path')
json_extract_double(json_column, 'path')

O SQL de wide table do Lindorm permite criar índices secundários para dados em caminhos específicos dentro de uma coluna do tipo JSON. No entanto, ao criar um índice secundário, especifique o tipo da função json_extract para a coluna JSON.

Acelerar consultas relacionadas a `json_contains`

json_contains(json_column, 'value', 'path')

Atualmente, os parâmetros value e path devem ser valores fixos. As condições de consulta devem ser estáticas.

Exemplos de criação de índice

-- Create an index for json_contains
CREATE INDEX idx_user_role ON test_table (json_contains(data, '"admin"', '$.roles')) INCLUDE(data) SYNC;

-- Create an index for json_extract_string
CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city')) INCLUDE(data) SYNC;

-- Create an index for json_extract_string without including columns
CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city'));

-- Create an index for json_extract_long
CREATE INDEX idx_user_age ON test_table (json_extract_long(data, '$.age')) INCLUDE(data) SYNC;

-- View the index creation result
SHOW INDEX FROM test_table;

Limitações

Sempre siga a sintaxe padrão ao usar JsonPath.
Esteja ciente do impacto no desempenho de operações JSON complexas.
Para caminhos consultados frequentemente, crie um índice para melhorar a velocidade de resposta da consulta.

Tratamento de erros comuns

Caminho JSON inválido

Ocorre um erro illegal json path se o JsonPath não for um JsonPath válido. Para obter mais informações, consulte Definição de JsonPath do MySQL.
Valor JSON inválido

json_contain candidate is not a valid value: Verifique se o parâmetro candidate é uma string JSON válida.
Aviso de varredura completa de tabela

This query may be a full table scan and thus may have unpredictable performance: Este aviso indica uma varredura completa de tabela ineficiente. Crie um índice para acelerar a consulta ou adicione uma cláusula LIMIT para filtrar um subconjunto dos dados.

Recomendações

Escolha a função de atualização correta

Escolha json_upsert ou json_set com base nas suas necessidades de negócio.
Planejamento de índices

Crie índices baseados em funções para caminhos JSON consultados frequentemente.
Segurança de tipos

Use funções de extração específicas de tipo para garantir a correção do tipo de dados. Evite misturar números e strings numéricas, pois isso pode causar ambiguidade. Por exemplo, evite misturar o número 10 e a string "10".

Visão geral das funções JSON

Descrições das funções

Funções construtoras de JSON

json_object

json_array

Funções de extração de JSON

json_extract

Funções de extração com segurança de tipos: json_extract_type

json_extract_string

json_extract_long

json_extract_double

Funções de verificação de contenção de JSON

json_contains

json_contains_any

Funções de atualização de JSON

json_set

json_insert

json_replace

json_remove

json_upsert

Resumo comparativo de funções

Comparação de funções de verificação de contenção de JSON

Comparação de funções de atualização de JSON

Suporte a índices baseados em funções

Sintaxe

Descrições dos parâmetros

Funções suportadas

Acelerar consultas relacionadas a json_extract e json_extract_type

Acelerar consultas relacionadas a json_contains

Exemplos de criação de índice

Limitações

Tratamento de erros comuns

Recomendações

Acelerar consultas relacionadas a `json_contains`