JSON_MODIFY (Transact-SQL)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics

Actualiza el valor de una propiedad en una cadena JSON y devuelve la cadena JSON actualizada.

Convenciones de sintaxis de Transact-SQL

Sintaxis

JSON_MODIFY ( expression , path , newValue )

Argumentos

expression

Expresión. Suele ser el nombre de una variable o una columna con texto JSON.

JSON_MODIFY devuelve un error si expression no contiene un valor JSON válido.

path

Expresión de ruta de acceso JSON que especifica la propiedad que se va a actualizar.

path tiene la siguiente sintaxis:

[append] [ lax | strict ] $.<json path>

• append

  Modificador opcional que especifica que el valor nuevo se debe anexar a la matriz a la que hace referencia <json path>.

• lax

  Especifica que la propiedad a la que hace <json path> referencia no tiene que existir. Si la propiedad no está presente, JSON_MODIFY intenta insertar el nuevo valor en la ruta de acceso especificada. Es posible que la inserción no se realice si la propiedad no se puede insertar en la ruta de acceso. Si no se especifica lax o strict, lax es el modo predeterminado.

• strict

  Especifica que la propiedad a la que hace referencia <json path> debe estar en la expresión JSON. Si la propiedad no está presente, JSON_MODIFY devuelve un error.

• <json path>

  Especifica la ruta de acceso de la propiedad que se va a actualizar. Para más información, vea Expresiones de ruta de acceso JSON (SQL Server).

  En SQL Server 2017 (14.x) y en Azure SQL Database, puede proporcionar una variable como el valor de path.

  JSON_MODIFY devuelve un error si el formato de path no es válido.

newValue

El nuevo valor de la propiedad especificada por path.

El nuevo valor debe ser varchar, nvarchar o text.

En el modo lax, JSON_MODIFY elimina la clave especificada si el nuevo valor es NULL.

JSON_MODIFY escape todos los caracteres especiales del nuevo valor si el tipo del valor es varchar o nvarchar. Un valor de texto no se escapa si tiene el formato JSON con el formato correcto generado por FOR JSON, JSON_QUERYo JSON_MODIFY.

Valor devuelto

Devuelve el valor actualizado de expression como texto con formato JSON correcto.

Comentarios

La JSON_MODIFY función permite actualizar el valor de una propiedad existente, insertar un nuevo par clave:valor o eliminar una clave basada en una combinación de modos y valores proporcionados.

En la tabla siguiente se compara el comportamiento de JSON_MODIFY en modo lax y modo strict. Para más información sobre la especificación del modo de ruta de acceso opcional (lax o strict), vea Expresiones de ruta de acceso JSON (SQL Server).

Valor nuevo	La ruta de acceso existe	Modo lax	Modo strict
NOT NULL	Sí	Se actualiza el valor existente.	Se actualiza el valor existente.
NOT NULL	No	Intente crear un nuevo par clave-valor en la ruta de acceso especificada. Esto podría producir un error. Por ejemplo, si especifica la ruta de acceso $.user.setting.theme, no inserta la clave theme si los $.user objetos o $.user.settings no existen, o si la configuración es una matriz o un valor JSON_MODIFY escalar.	Error: INVALID_PROPERTY
NULL	Sí	Se elimina la propiedad existente.	El valor actual se establece en NULL.
NULL	No	No sucede nada. El primer argumento se devuelve como resultado.	Error: INVALID_PROPERTY

En el modo lax, JSON_MODIFY intenta crear un par clave-valor, pero en algunos casos se podría producir un error.

Las funciones JSON funcionan igual si el documento JSON se almacena en varchar, nvarchar o el tipo de datos json nativo.

Ejemplos

A Operaciones básicas

En el siguiente ejemplo se muestran las operaciones básicas que se pueden realizar con texto JSON.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update name
SET @info = JSON_MODIFY(@info, '$.name', 'Mike');
PRINT @info;

-- Insert surname
SET @info = JSON_MODIFY(@info, '$.surname', 'Smith');
PRINT @info;

-- Set name NULL
SET @info = JSON_MODIFY(@info, 'strict $.name', NULL);
PRINT @info;

-- Delete name
SET @info = JSON_MODIFY(@info, '$.name', NULL);
PRINT @info;

-- Add skill
SET @info = JSON_MODIFY(@info, 'append $.skills', 'Azure');
PRINT @info;

El conjunto de resultados es el siguiente:

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL"],
    "surname": "Smith"
} {
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

B. Varias actualizaciones

Con JSON_MODIFY, solo puede actualizar una propiedad. Si tiene que realizar varias actualizaciones, puede usar varias JSON_MODIFY llamadas.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Multiple updates
SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.name', 'Mike'), '$.surname', 'Smith'), 'append $.skills', 'Azure');
PRINT @info;

El conjunto de resultados es el siguiente:

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "Mike",
    "skills": ["C#", "SQL", "Azure"],
    "surname": "Smith"
}

C. Cambiar el nombre de una clave

En el ejemplo siguiente se muestra cómo cambiar el nombre de una propiedad en texto JSON con la JSON_MODIFY función . En primer lugar, puede tomar el valor de una propiedad existente e insertarlo como un nuevo par clave-valor. A continuación, puede eliminar la clave antigua estableciendo el valor de la propiedad antigua en NULL.

DECLARE @product NVARCHAR(100) = '{"price":49.99}';
PRINT @product;

-- Rename property
SET @product = JSON_MODIFY(JSON_MODIFY(@product, '$.Price', CAST(JSON_VALUE(@product, '$.price') AS NUMERIC(4, 2))), '$.price', NULL);
PRINT @product;

El conjunto de resultados es el siguiente:

{
    "price": 49.99
} {
    "Price": 49.99
}

Si no convierte el nuevo valor a un tipo numérico, JSON_MODIFY lo considera como texto y lo coloca entre comillas dobles.

D. Incremento de un valor

En el siguiente ejemplo se muestra cómo aumentar el valor de una propiedad en texto JSON con la función JSON_MODIFY. En primer lugar, puede tomar el valor de la propiedad existente e insertarlo como un nuevo par clave-valor. A continuación, puede eliminar la clave antigua estableciendo el valor de la propiedad antigua en NULL.

DECLARE @stats NVARCHAR(100) = '{"click_count": 173}';
PRINT @stats;

-- Increment value
SET @stats = JSON_MODIFY(@stats, '$.click_count', CAST(JSON_VALUE(@stats, '$.click_count') AS INT) + 1);
PRINT @stats;

El conjunto de resultados es el siguiente:

{
    "click_count": 173
} {
    "click_count": 174
}

E. Modificación de un objeto JSON

JSON_MODIFY trata el argumento newValue como texto sin formato incluso cuando contiene texto con formato JSON correcto. Como resultado, la salida JSON de la función se inserta entre comillas dobles y todos los caracteres especiales son caracteres de escape, tal y como se muestra en el siguiente ejemplo.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', '["C#","T-SQL","Azure"]');
PRINT @info;

El conjunto de resultados es el siguiente:

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": "[\"C#\",\"T-SQL\",\"Azure\"]"
}

Para evitar el escape automático, proporcione un newValue con la función JSON_QUERY. JSON_MODIFY sabe que el valor devuelto por JSON_QUERY tiene un formato JSON correcto, por lo que no escapa del valor.

DECLARE @info NVARCHAR(100) = '{"name":"John","skills":["C#","SQL"]}';
PRINT @info;

-- Update skills array
SET @info = JSON_MODIFY(@info, '$.skills', JSON_QUERY('["C#","T-SQL","Azure"]'));
PRINT @info;

El conjunto de resultados es el siguiente:

{
    "name": "John",
    "skills": ["C#", "SQL"]
} {
    "name": "John",
    "skills": ["C#", "T-SQL", "Azure"]
}

F. Actualización de una columna JSON

En el siguiente ejemplo se actualiza el valor de una propiedad en una columna de tabla que contiene JSON.

UPDATE Employee
SET jsonCol = JSON_MODIFY(jsonCol, '$.info.address.town', 'London')
WHERE EmployeeID = 17;

Contenido relacionado

• Expresiones de ruta de acceso JSON (SQL Server)
• Datos JSON (SQL Server)