JSON_MODIFY (Transact-SQL)

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics sql analytics endpointin Microsoft FabricWarehouse in Microsoft FabricSQL Database in Microsoft Fabric SQL Database in Microsoft Fabric

La JSON_MODIFY sintaxis actualiza el valor de una propiedad en una cadena JSON y devuelve la cadena JSON actualizada.

Convenciones de sintaxis de Transact-SQL

Syntax

JSON_MODIFY ( expression , path , newValue )

Arguments

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 JSON válido.

path

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

path tiene la sintaxis siguiente:

[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 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 obtener más información, consulte Expresiones de ruta de acceso JSON.

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

  JSON_MODIFY devuelve un error si el formato de la ruta de acceso no es válido.

newValue

Nuevo valor de la propiedad especificada por ruta de acceso.

El nuevo valor debe ser varchar, nvarchar, char, tinyint, smallint, int, bigint, bit, decimal/numeric o float real/. No se admite el tipo de datos de texto .

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 la expresión como texto JSON con el formato correcto.

Remarks

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 obtener más información sobre la especificación del modo de ruta de acceso opcional (lax o strict), consulte Expresiones de ruta de acceso JSON.

Nuevo valor	Existe el camino	Modo laxo	Modo estricto
NOT NULL	Yes	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 JSON_MODIFY si los theme objetos o $.user no existen, o si la configuración es una matriz o un valor $.user.settings escalar.	Error: INVALID_PROPERTY
NULL	Yes	Se elimina la propiedad existente.	El valor actual se establece en NULL.
NULL	No	Sin acción. 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.

Examples

A. Operaciones Básico

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;

Este es el conjunto de resultados.

{
    "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. Múltiples 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;

Este es el conjunto de resultados.

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

Este es el conjunto de resultados.

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

Este es el conjunto de resultados.

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

E. Modificación de un objeto JSON

JSON_MODIFY trata el argumento newValue como texto sin formato aunque contenga texto JSON con el formato 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;

Este es el conjunto de resultados.

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

Para evitar el escape automático, proporcione newValue mediante la JSON_QUERY función . 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;

Este es el conjunto de resultados.

{
    "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
• Datos JSON en SQL Server