Funciones JSON - Lindorm - Centro de documentación de Alibaba Cloud

Este documento describe el uso, los parámetros y ejemplos de todas las funciones JSON en Lindorm SQL.

Descripción general de las funciones JSON

Clasificación de la función	Nombre de la función	Descripción de la función
Funciones constructoras de JSON	json_object	Crea un objeto JSON.
Funciones constructoras de JSON	json_array	Crea un arreglo JSON.
Funciones de extracción de JSON	json_extract	Extrae un valor de un documento JSON en una ruta especificada.
	json_extract_string	Extrae un valor en una ruta especificada y lo convierte a cadena de texto.
	json_extract_long	Extrae un valor en una ruta especificada y lo convierte a entero largo.
	json_extract_double	Extrae un valor en una ruta especificada y lo convierte a número de punto flotante.
Funciones de verificación de contención de JSON	json_contains	Comprueba si un documento JSON contiene todos los valores u objetos especificados.
Funciones de verificación de contención de JSON	json_contains_any	Comprueba si un documento JSON contiene alguno de los valores u objetos especificados.
Funciones de actualización de JSON	json_set	Inserta o actualiza datos en un documento JSON y devuelve el nuevo documento. Equivale a `json_insert` + `json_replace`.
	json_insert	Inserta datos en un documento JSON y devuelve el nuevo documento. Solo inserta un campo nuevo si este no existe.
	json_replace	Reemplaza los datos existentes en un documento JSON y devuelve el nuevo documento. Solo actualiza el valor si el campo existe.
	json_remove	Elimina datos de un documento JSON en una ruta especificada y devuelve el documento modificado.
	json_upsert	Semánticamente igual que `json_set`, pero admite valores NULL.

Descripciones de las funciones

Funciones constructoras de JSON

json_object

Crea un objeto JSON.

Sintaxis:

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

Parámetros:

key: El nombre de la clave. Debe ser una cadena de texto.
value: El valor. Puede ser de cualquier tipo.

Ejemplos:

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

Crea un arreglo JSON.

Sintaxis:

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

Parámetros:

value: El valor. Puede ser un escalar, un objeto o un arreglo.

Ejemplos:

-- 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'));

Funciones de extracción de JSON

json_extract

Extrae un valor de un documento JSON en una ruta especificada.

Sintaxis:

json_extract(json_column, 'path')

Parámetros:

json_column: Una columna o expresión de tipo JSON.
path: Una expresión de ruta JSON, como $.field, $.array[0] o $.nested.field.

Ejemplos:

-- 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';

Funciones de extracción con seguridad de tipos: json_extract_type

json_extract_string

Extrae un valor en una ruta especificada y lo convierte a cadena de texto. Si el valor en la ruta no es una cadena de texto, devuelve NULL de forma predeterminada.

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

Extrae un valor en una ruta especificada y lo convierte a entero largo. Si el valor en la ruta no es un número, Java Database Connectivity (JDBC) genera un error.

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

Extrae un valor en una ruta especificada y lo convierte a número de punto flotante. Si el valor en la ruta no es un número de punto flotante, JDBC genera un error.

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

Estas tres funciones tienen tipado fuerte. El elemento en la ruta especificada debe coincidir estrictamente con el tipo de la función. De lo contrario, la función devuelve NULL o genera un error. Es posible configurar el clúster para forzar la conversión de tipos, aunque no se recomienda. Se recomienda definir los tipos de datos antes de insertar datos JSON, ya que los tipos mixtos pueden provocar problemas lógicos inesperados.

Funciones de verificación de contención de JSON

json_contains

Comprueba si un documento JSON contiene todos los valores u objetos especificados.

Sintaxis:

json_contains(target, candidate[, path])

Parámetros:

target_json: Obligatorio. El documento JSON.
candidate_json: Obligatorio. El documento JSON que se busca.
path: Opcional. La expresión de ruta.

Para obtener más información sobre los parámetros, consulte Additional parameter information.

Valor devuelto:

1 (verdadero): se encontró el valor especificado.
0 (falso): el destino no contiene el candidato.

La función json_contains devuelve 1 si el documento target_json contiene el documento candidate_json, y 0 en caso contrario. Si especifica el parámetro path, la función comprueba si los datos en la path especificada contienen el documento candidate_json.

La función json_contains devuelve NULL en los siguientes casos:

La ruta especificada no existe en el documento JSON.
Cualquier parámetro es NULL.

Ejemplos:

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

Información adicional sobre los parámetros

candidate_json es un documento JSON. Debe representar el elemento correspondiente como una cadena de texto JSON.
- Documento JSON que contiene el número 10: '10'
- Documento JSON que contiene la cadena "10": '"10"'
- Documento JSON que contiene una lista de números: '[1,2,3]'
- Documento JSON que contiene una lista de cadenas de texto: '["10","abc","key"]'
Un subconjunto de un escalar solo puede ser un escalar. Un subconjunto de un arreglo puede ser un elemento o un arreglo.

Clasificaciones de tipos JSON:
- Escalar: String, Number (Integer/Double), Boolean, Null
- Tipo complejo: Array, Object
Considere la condición de verificación json_contains_any(skills, '["Java"]', '$.technical_skills'). Para el JSON en la ruta $.technical_skills:
- Si el valor es {"technical_skills":"Java"}, la función devuelve false. Un subconjunto de una cadena de texto no puede ser una lista.
- Si el valor es {"technical_skills":["Java"]} o {"technical_skills":["Java","Go"]}, la función devuelve true.
Considere la condición de verificación json_contains_any(skills, '"Java"', '$.technical_skills') . Para el JSON en la ruta $.technical_skills:
- Si el valor es {"technical_skills":"Java"}, la función devuelve true.
- Si el valor es {"technical_skills":["Java"]} o {"technical_skills":["Java","Go"]}, la función devuelve true.

json_contains_any

Comprueba si un documento JSON contiene alguno de los valores u objetos especificados.

Sintaxis:

La sintaxis es la misma que para json_contains. Para obtener más información sobre las diferencias, consulte Comparison of JSON containment check functions.

Ejemplos:

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

Funciones de actualización de JSON

json_set

Inserta o actualiza datos en un documento JSON y devuelve el documento modificado. Esta función equivale a json_insert + json_replace.

Sintaxis:

json_set(json_column, 'path', new_value)

Comportamiento:

Si la ruta especificada existe, esta función actualiza el valor en esa ruta.
Si la ruta especificada no existe, esta función inserta el valor en esa ruta.
Si la columna JSON es NULL, el resultado también es NULL. No se ejecuta ninguna acción.

Ejemplos:

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

Inserta datos en un documento JSON y devuelve el documento modificado. Esta función inserta un valor nuevo solo si no existe un valor en la ruta especificada.

Sintaxis:

json_insert(json_column, 'path', new_value)

Comportamiento:

Si la ruta especificada no existe, esta función inserta el valor en esa ruta.
Si la ruta especificada ya existe, esta función no ejecuta ninguna acción.

Ejemplos:

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

Reemplaza los datos existentes en un documento JSON y devuelve el documento modificado. Esta función actualiza un valor solo si existe un valor en la ruta especificada.

Sintaxis:

json_replace(json_column, 'path', new_value)

Comportamiento:

Si la ruta especificada existe, esta función actualiza el valor en esa ruta.
Si la ruta especificada no existe, esta función no ejecuta ninguna acción.

Ejemplos:

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

Elimina datos de un documento JSON en una ruta especificada y devuelve el documento modificado.

Sintaxis:

json_remove(json_column, 'path')

Comportamiento:

Si la ruta especificada existe, esta función elimina los datos en esa ruta.
Si la ruta especificada no existe, la operación es segura y no se produce ningún error.

Ejemplos:

-- 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 función es semánticamente igual que json_set, pero admite valores NULL.

Sintaxis:

json_upsert(json_column, 'path', new_value)

Comportamiento:

Si la columna JSON es NULL, esta función crea un objeto nuevo: {path: new_value}
Si la ruta especificada existe, esta función actualiza el valor en esa ruta.
Si la ruta especificada no existe, esta función inserta el valor en esa ruta.

Ejemplos:

-- 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 obtener más información sobre las diferencias entre estas funciones de actualización de JSON, consulte Comparison of JSON update functions.

Resumen de comparación de funciones

Comparación de las funciones de verificación de contención de JSON

Función

Diferencia

json_contains

En json_contains, cada elemento de candidate_json también debe ser un elemento de target_json.

Es decir, candidate_json debe ser un subconjunto de target_json.

json_contains_any

En json_contains_any, como mínimo un elemento de candidate_json debe ser también un elemento de target_json.

Es decir, candidate_json y target_json deben tener como mínimo un elemento en común.

Comparación de las funciones de actualización de JSON

Función	Manejo de columnas NULL	El campo existe	El campo no existe	Caso de uso principal
`json_set`	Sigue siendo NULL	Actualización	Inserción	Actualiza una columna que se sabe que no es NULL. (Se alinea con la semántica de MySQL).
`json_upsert`	Crea un objeto nuevo	Actualización	Inserción	Actualizaciones de propósito general. Inserta incluso si la columna es NULL.
`json_insert`	Sigue siendo NULL	Sin operación	Inserción	Inserta únicamente campos nuevos.
`json_replace`	Sigue siendo NULL	Actualización	Sin operación	Actualiza únicamente campos existentes.
`json_remove`	Sigue siendo NULL	Eliminación	Sin operación	Elimina campos.

Compatibilidad con índices basados en funciones

Sintaxis

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 ')'

Descripciones de los parámetros

Parámetro	Descripción
index_name	El nombre de la tabla de índice.
table_name	El nombre de la tabla ancha.
json_extract_type	Extrae un campo de un tipo de datos específico de una columna JSON para usarlo como índice secundario. Si el tipo de datos no coincide, no se construye el índice secundario. Se admiten los siguientes tipos de funciones: json_extract_string json_extract_long json_extract_double
column	El nombre de la columna JSON.
json_path	La ruta en la columna JSON. Se usa para extraer el valor en la ruta especificada.
ASYNC	Construye el índice de forma asíncrona. Si no agrega ASYNC, el índice se construye de forma síncrona.

Para obtener más información sobre la compatibilidad de los índices de búsqueda con las funciones JSON, consulte Use search indexes for the Lindorm JSON type.

Funciones compatibles

A continuación se muestran las funciones JSON que admiten la creación de índices basados en funciones:

Acelerar consultas relacionadas con `json_extract` y `json_extract_type`

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

Lindorm wide table SQL permite crear índices secundarios para los datos en rutas específicas dentro de una columna de tipo JSON. Sin embargo, al construir un índice secundario, debe especificar el tipo de la función json_extract para la columna JSON.

Acelerar consultas relacionadas con `json_contains`

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

Actualmente, los parámetros value y path deben ser valores fijos. Las condiciones de consulta deben ser estáticas.

Ejemplos de creación de índices

-- 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;

Limitaciones

Siga siempre la sintaxis estándar cuando use JsonPath.
Tenga en cuenta el impacto en el rendimiento de las operaciones JSON complejas.
Para las rutas consultadas con frecuencia, cree un índice para mejorar la velocidad de respuesta de las consultas.

Manejo de errores comunes

Ruta JSON no válida

Se produce un error illegal json path si JsonPath no es un JsonPath válido. Para obtener más información, consulte la definición de JsonPath de MySQL.
Valor JSON no válido

json_contain candidate is not a valid value: Compruebe que el parámetro candidate sea una cadena de texto JSON válida.
Advertencia de exploración completa de la tabla

This query may be a full table scan and thus may have unpredictable performance: Esta advertencia indica una exploración completa de la tabla ineficiente. Cree un índice para acelerar la consulta o agregue una cláusula LIMIT para filtrar un subconjunto de los datos.

Recomendaciones

Elija la función de actualización adecuada

Elija json_upsert o json_set según sus necesidades de negocio.
Planificación de índices

Cree índices basados en funciones para las rutas JSON que consulte con frecuencia.
Seguridad de tipos

Use funciones de extracción específicas por tipo para garantizar la corrección de los tipos de datos. Evite mezclar números y cadenas numéricas, ya que esto puede generar ambigüedad. Por ejemplo, evite mezclar el número 10 y la cadena "10".

Descripción general de las funciones JSON

Descripciones de las funciones

Funciones constructoras de JSON

json_object

json_array

Funciones de extracción de JSON

json_extract

Funciones de extracción con seguridad de tipos: json_extract_type

json_extract_string

json_extract_long

json_extract_double

Funciones de verificación de contención de JSON

json_contains

json_contains_any

Funciones de actualización de JSON

json_set

json_insert

json_replace

json_remove

json_upsert

Resumen de comparación de funciones

Comparación de las funciones de verificación de contención de JSON

Comparación de las funciones de actualización de JSON

Compatibilidad con índices basados en funciones

Sintaxis

Descripciones de los parámetros

Funciones compatibles

Acelerar consultas relacionadas con json_extract y json_extract_type

Acelerar consultas relacionadas con json_contains

Ejemplos de creación de índices

Limitaciones

Manejo de errores comunes

Recomendaciones

Acelerar consultas relacionadas con `json_extract` y `json_extract_type`

Acelerar consultas relacionadas con `json_contains`