极客教程SQL JSON_MODIFY()函数
您可以使用SQL JSON_MODIFY() 函数来更改SQL Server表的列中保存的JSON数据。这个函数是为了方便在SQL Server中存储、处理和查询JSON数据而添加到JSON函数系列中的。这个函数首次在SQL Server 2016中提供。
JSON_MODIFY()函数可以用于更新JSON字符串。可以用它来更新以下内容：
- 更新现有属性值
-
在现有数组中添加新元素
-
从JSON字符串中删除属性
-
删除属性
语法
以下是SQL JSON_MODIFY()函数的语法−
JSON_MODIFY ( expression , path , newValue )
参数
- expression −通常是列的名称或包含JSON文本的变量。
-
path −JSON路径表达式，用于指定要更新的属性。path的语法如下：
[append] [lax | strict] $.<json path>
append - 它是一个可选的修饰符，指定新值应该被附加到与指定的数组中。
lax - 它指定由引用的属性可以不存在。如果属性不存在，则JSON_MODIFY尝试在指定的路径上插入新值。
strict - 它指定由引用的属性必须存在于JSON表达式中。如果属性不存在，它将返回一个错误。
< json path> - 它指定要更新的属性的路径。
- newvalue - 由路径指示的属性的新值。值必须是varchar或text类型。
示例
让我们尝试使用以下查询更新JSON字符串的属性值 –
DECLARE @work VARCHAR(150) = '{"car":"RX100", "Price":45000}'
SELECT
JSON_MODIFY(@work,'$.Item', 'AUDI') AS UpdatedValue;
输出
当我们执行上述查询时，输出结果如下：
+---------------------------------------------------------+
| UpdatedValue |
+---------------------------------------------------------+
| {"car":"RX100", "Price":45000,"Item":"AUDI"} |
+---------------------------------------------------------+
示例
让我们看另一种情况，我们将使用以下查询来更新JSON字符串的值：
SELECT JSON_MODIFY('{"Place": "INDIA"}', '$.Place', 'DUBAI') AS 'Result';
输出
执行上述查询后，输出如下所示 –
+----------------------------------------------+
| Result |
+----------------------------------------------+
| {"Place": "DUBAI"} |
+----------------------------------------------+
示例
在下面的示例中，我们将使用以下查询将新的属性和值插入JSON字符串中：
DECLARE @work VARCHAR(150) = '{"car":"BMW","Price":2500000}'
DECLARE @path VARCHAR(100) = '$.Color'
DECLARE @newone VARCHAR(50) = 'Green'
SELECT JSON_MODIFY(@work,@Path, @newone) AS UpdatedValue;
输出
当我们执行上面的查询时，输出结果如下：
+---------------------------------------------------------+
| UpdatedValue |
+---------------------------------------------------------+
| {"car":"BMW","Price":2500000,"Color":"Green"} |
+---------------------------------------------------------+
示例
让我们看看另一种情况，我们将使用以下查询来添加包含数组的新属性值-
DECLARE @work VARCHAR(4000)
DECLARE @new VARCHAR(256) = N'["Engine","Wipers","DieselTank"]';
Set @work='{"Car":"BMW","Price":"2000000"}' Select JSON_MODIFY(@work,'$.SpareParts',@new) AS 'UpdatedValue';
输出
执行以上查询后，输出结果如下：
+--------------------------------------------------------------------------------------+
| UpdatedValue |
+--------------------------------------------------------------------------------------+
| {"Car":"BMW","Price":"2000000","SpareParts":"[\"Engine\",\"Wipers\",\"DieselTank\"]"}|
+--------------------------------------------------------------------------------------+
示例
让我们来看一个示例，我们将使用以下查询来更新JSON数据并检索原始和更新后的JSON输出。
DECLARE @work VARCHAR(4000)
SET @work= '{"Beach": "ANDAMAN"}'
SELECT @work AS 'OriginalValue',
JSON_MODIFY(@work, '$.Beach', 'GOA') AS 'ModifiedValue';
输出
当我们执行上面的查询时，输出结果如下：
+--------------------------+------------------------+
| OriginalValue | ModifiedValue |
+--------------------------+------------------------+
| {"Beach": "ANDAMAN"} | {"Beach": "GOA"} |
+--------------------------+------------------------+
示例
让我们来看以下示例，我们将使用以下查询来重命名键-
DECLARE @work VARCHAR(4000)
SET @work = '{"Brand":"HP"}';
SELECT @work AS 'OriginalValue',
JSON_MODIFY(
JSON_MODIFY(@work, '.Company', JSON_VALUE(@work,'.Brand')),
'$.Brand',NULL) AS UpdatedValue;
输出
执行上述查询后，输出如下：
+----------------+--------------------+
|OriginalValue | UpdatedValue |
+----------------+--------------------+
| {"Brand":"HP"} | {"Company":"HP"} |
+----------------+--------------------+
示例
让我们考虑另一种情况，我们将使用以下查询对JSON数据进行多次更改：
DECLARE @work VARCHAR(4000), @new VARCHAR(100);
SET @work = '{"Location":"Himalayas","Place":"Mountains"}';
SET @new = '["Sheep","SnowBear"]';
SELECT @work AS 'OriginalValue',
JSON_MODIFY(JSON_MODIFY(@work, '.Animals', JSON_QUERY(@new)), '.Location', 'Shimla') AS 'UpdatedValue';
输出
在执行以上查询时，输出如下所示 –
+----------------------------------------------+----------------------------------------------------------------------------+
| OriginalValue | UpdatedValue |
+----------------------------------------------+----------------------------------------------------------------------------+
| {"Location":"Himalayas","Place":"Mountains"} | {"Location":"Shimla","Place":"Mountains","Animals":["Sheep","SnowBear"]} |
+----------------------------------------------+----------------------------------------------------------------------------+
示例
让我们来看下面的示例，我们将使用以下查询来增加JSON数据。
DECLARE @work VARCHAR(100)='{"click_count": 140}'
PRINT @work
SET @work=JSON_MODIFY(@work,'.click_count',
CAST(JSON_VALUE(@work,'.click_count') AS INT)+3)
PRINT @work
输出
执行上述查询后，输出如下所示-
+----------------------+
| {"click_count": 140} |
+----------------------+
| {"click_count": 143} |
+----------------------+