json - How to update a nested array in JSON in mssql
问题描述
I am using mssql and one column is having json data, I want to update that part of that json which is an array, by passing the id.
{
"customerName":"mohan",
"custId":"e35273d0-c002-11e9-8188-a1525f580dfd",
"feeds":[
{
"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e",
"feedName":"ccsdcdscsdc",
"format":"Excel",
"sources":[
{
"sourceId":69042417,
"name":"TV 2 Livsstil"
},
{
"sourceId":69042419,
"name":"Turk Max"
}
]
},
{
"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e",
"feedName":"dfgdfgdfgdfgsdfg",
"format":"XmlTV",
"sources":[
{
"sourceId":69042417,
"name":"TV 2 Livsstil"
},
{
"sourceId":69042419,
"name":"Turk Max"
}
]
}
]
}
suppose if I am going to pass customerId and feedId, it should update the whole feed with the feed which I have passed.
I tried with below query, but no help.
UPDATE
ExtractsConfiguration.dbo.Customers
SET
configJSON = JSON_MODIFY(configJSON,'$.feeds[]',{"feedName":"ccsdcdscsdc"})
WHERE
CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
AND json_query(configJSON,'$.feeds[].feedId'='57f221d0-c310-11e9-8af7-cf1cf42fc72e');
解决方案
@mohan，这是一个棘手的问题，我将其视为对自己的挑战。有一种方法可以按照您的要求更新嵌套 JSON 对象的值，但是，它并不像看起来那么简单。
因为您在数组中工作，所以需要数组的索引才能更新嵌套值。在您的情况下，您不知道数组中的索引，但是，您确实有一个可以引用的键值，在这种情况下，您的feedName。
为了更新您的值，您首先需要“解包”您的 JSON，以便您可以过滤特定的 feedName，在您的示例中为“ccsdcdscsdc”。
这是一个您可以在 SSMS 中运行的示例，它将使您朝着正确的方向前进。
我创建的第一件事是 @Customers TABLE 变量来模仿您在示例中显示的数据结构并插入您的示例数据。
DECLARE @Customers TABLE ( CustomerId VARCHAR(50), configJSON VARCHAR(MAX) );
INSERT INTO @Customers ( CustomerID, configJSON ) VALUES ( '9ee07040-c001-11e9-b29a-55eb3439cd7c', '{"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]}' );
对 @Customers 运行 SELECT 会返回以下内容：
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CustomerId | configJSON |
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 9ee07040-c001-11e9-b29a-55eb3439cd7c | {"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]} |
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
接下来，我匹配了您的更新规则：更新限制为特定 CustomerId (9ee07040-c001-11e9-b29a-55eb3439cd7c) 和 feedName (ccsdcdscsdc) 的嵌套 JSON 值。
就像我提到的，我们需要先“解包” JSON，因为我们不知道应该更新的特定键（索引）值。完成这两项任务（解包/更新）的最简单方法是使用公用表表达式 (CTE)。
所以，我是这样做的：
;WITH Config_CTE AS (
SELECT * FROM @Customers AS Customer
CROSS APPLY OPENJSON( configJSON, '$.feeds' ) AS Config
WHERE
Customer.CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
AND JSON_VALUE( Config.value, '$.feedName' ) = 'ccsdcdscsdc'
)
UPDATE Config_CTE
SET configJSON = JSON_MODIFY( configJSON, '$.feeds[' + Config_CTE.[key] + '].format', 'MS Excel' );
CTE 允许我们“解包”（我把这个词编出来，因为它看起来很合适）包含在configJSON中的 JSON ，然后允许我们对feedName应用过滤器。
AND JSON_VALUE( Config.value, '$.feedName' ) = 'ccsdcdscsdc'
您还会注意到我们包含了 CustomerId 规则：
Customer.CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
CustomerId和feedName都可以很容易地成为 SQL 变量。
那么，这是做什么的呢？如果我们要查看 Configs_CTE 结果集（通过将 UPDATE... 更改为 SELECT * FROM Config_CTE ），我们会看到：
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| CustomerId | configJSON | key | value | type |
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| 9ee07040-c001-11e9-b29a-55eb3439cd7c | {"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]} | 0 | {"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]} | 5 |
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
这里有很多信息，但我们真正关心的是“关键”列，因为它包含我们要更新的提要索引（在本例中为 0 ）。
这样，对于 feedName 为“ccsdcdscsdc”的“feed”，能够完成从“Excel”到“MS Excel”的请求和更新格式。
这家伙（注意使用 Config_CTE.[key] ）：
UPDATE Config_CTE
SET configJSON = JSON_MODIFY( configJSON, '$.feeds[' + Config_CTE.[key] + '].format', 'MS Excel' );
它奏效了吗？让我们看看更新后的表的数据。
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CustomerId | configJSON |
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 9ee07040-c001-11e9-b29a-55eb3439cd7c | {"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"MS Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]} |
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
这是更新后的 JSON“美化”（很确定我没有编造那个）。
{
"customerName": "mohan",
"custId": "e35273d0-c002-11e9-8188-a1525f580dfd",
"feeds": [{
"feedId": "57f221d0-c310-11e9-8af7-cf1cf42fc72e",
"feedName": "ccsdcdscsdc",
"format": "MS Excel",
"sources": [{
"sourceId": 69042417,
"name": "TV 2 Livsstil"
}, {
"sourceId": 69042419,
"name": "Turk Max"
}]
}, {
"feedId": "59bbd360-c312-11e9-8af7-cf1cf42fc72e",
"feedName": "dfgdfgdfgdfgsdfg",
"format": "XmlTV",
"sources": [{
"sourceId": 69042417,
"name": "TV 2 Livsstil"
}, {
"sourceId": 69042419,
"name": "Turk Max"
}]
}]
}
好了，你知道了，feedName “ ccsdcdscsdc”的格式已从“Excel”更新为“MS Excel”。我不清楚您要更新什么，所以我使用格式进行测试/示例。
我希望这能让你朝着正确的方向前进。快乐编码！
这是可以在 SSMS 中运行的完整示例：
-- CREATE A CUSTOMERS TABLE TO MIMIC SCHEMA --
DECLARE @Customers TABLE ( CustomerId VARCHAR(50), configJSON VARCHAR(MAX) );
INSERT INTO @Customers ( CustomerID, configJSON ) VALUES ( '9ee07040-c001-11e9-b29a-55eb3439cd7c', '{"customerName":"mohan","custId":"e35273d0-c002-11e9-8188-a1525f580dfd","feeds":[{"feedId":"57f221d0-c310-11e9-8af7-cf1cf42fc72e","feedName":"ccsdcdscsdc","format":"Excel","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]},{"feedId":"59bbd360-c312-11e9-8af7-cf1cf42fc72e","feedName":"dfgdfgdfgdfgsdfg","format":"XmlTV","sources":[{"sourceId":69042417,"name":"TV 2 Livsstil"},{"sourceId":69042419,"name":"Turk Max"}]}]}' );
-- SHOW CURRENT DATA --
SELECT * FROM @Customers;
-- UPDATE "format" FROM "Excel" to "MS Excel" FOR feedName: ccsdcdscsdc --
WITH Config_CTE AS (
SELECT * FROM @Customers AS Customer
CROSS APPLY OPENJSON( configJSON, '$.feeds' ) AS Config
WHERE
Customer.CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
AND JSON_VALUE( Config.value, '$.feedName' ) = 'ccsdcdscsdc'
)
UPDATE Config_CTE
SET configJSON = JSON_MODIFY( configJSON, '$.feeds[' + Config_CTE.[key] + '].format', 'MS Excel' );
-- SHOW UPDATED DATA --
SELECT * FROM @Customers;
编辑：
我想用给定的 feedId 用整个新的提要更新提要
要用全新的提要替换一个“提要”，您可以执行以下操作：
-- REPLACE AN ENTIRE JSON ARRAY OBJECT --
DECLARE @MyNewJson NVARCHAR(MAX) = '{"feedId": "this_is_an_entirely_new_node","feedName": "ccsdcdscsdc","format": "NewFormat","sources": [{"sourceId": 1,"name": "New Source 1"},{"sourceId": 2,"name": "New Source 2"}]}';
WITH Config_CTE AS (
SELECT * FROM @Customers AS Customer
CROSS APPLY OPENJSON( configJSON, '$.feeds' ) AS Config
WHERE
Customer.CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
AND JSON_VALUE( Config.value, '$.feedName' ) = 'ccsdcdscsdc'
)
UPDATE Config_CTE
SET configJSON = JSON_MODIFY( configJSON, '$.feeds[' + Config_CTE.[key] + ']', JSON_QUERY( @MyNewJson ) );
运行此程序后，提要现在显示为：
{
"customerName": "mohan",
"custId": "e35273d0-c002-11e9-8188-a1525f580dfd",
"feeds": [
{
"feedId": "this_is_an_entirely_new_node",
"feedName": "ccsdcdscsdc",
"format": "NewFormat",
"sources": [
{
"sourceId": 1,
"name": "New Source 1"
},
{
"sourceId": 2,
"name": "New Source 2"
}
]
},
{
"feedId": "59bbd360-c312-11e9-8af7-cf1cf42fc72e",
"feedName": "dfgdfgdfgdfgsdfg",
"format": "XmlTV",
"sources": [
{
"sourceId": 69042417,
"name": "TV 2 Livsstil"
},
{
"sourceId": 69042419,
"name": "Turk Max"
}
]
}
]
}
注意JSON_QUERY( @MyNewJson )UPDATE 中的使用。这个很重要。
来自微软的文档：
没有可选第二个参数的 JSON_QUERY 只返回第一个参数作为结果。由于 JSON_QUERY 总是返回有效的 JSON，FOR JSON 知道这个结果不必被转义。
如果您要在没有 JSON_QUERY 的情况下传递 @MyNewJson，那么您的新 json 将被转义（例如，“customerName”变为 \"customerName\"），就好像它被存储为纯文本一样。JSON_QUERY 将返回未转义的有效 JSON，这在您的情况下是必需的。
另请注意，我为替换整个提要与单个项目值所做的唯一更改是切换
'$.feeds[' + Config_CTE.[key] + '].format'
至
'$.feeds[' + Config_CTE.[key] + ']'.
推荐阅读
- javascript - 如何使用带有递归获取的承诺？
- php - 如何使用 Laravel 5.6 升级 Carbon？
- eos - 什么是正确的cmd。EOS cmd 的行调用。“克莱奥斯定契约？”
- javascript - 为什么 Web 服务不能正确返回字符串？
- c# - 如何将属性与 DataGrid 中的复选框绑定？
- apache - 如何在 Apache Knox 中启用 SSO？
- python - 在python中正确分配数据结构
- fortran - 无论如何检查矩阵行中的n个项在fortran中是否相等？
- azure - 应用服务 HTTP/2 不工作
- python - 带有链式比较的 PyCharm 输入警告