如何在MSSQL中更新JSON中的嵌套数组 [英] How to update a nested array in JSON in mssql
问题描述
我正在使用mssql，其中一列包含json数据，我想通过传递id更新该json的一部分，即数组.
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"
}
]
}
]
}
假设我要通过customerId和feedId，它应该使用我通过的供稿更新整个供稿.
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对象的值，但是，它看起来并不简单.
This, @mohan, is a tricky one and I took it on as a challenge to myself. There is a way to update a nested JSON object's value like you're asking, however, it's not as straight forward as it seems.
因为要在数组中工作，所以需要数组的索引才能更新嵌套值.就您而言，您不知道数组内的索引，但是，您确实有一个可以引用的键值，在这种情况下，您的 feedName .
Because you're working within an array, you need the array's index in order to update a nested value. In your case you don't know the index within the array, however, you do have a key-value you can reference, in this case, your feedName.
为了更新您的值，您首先需要解压缩" JSON，以便您可以过滤特定的feedName，在示例中为"ccsdcdscsdc".
In order to update your value, you first need to "unpack" your JSON so that you can filter for a specific feedName, "ccsdcdscsdc" in your example.
以下是您可以在SSMS中运行的示例，它将使您朝正确的方向前进.
Here is an example that you can run in SSMS that will get you moving in the right direction.
我创建的第一件事是@Customers TABLE变量，以模仿您在示例中显示的数据结构并插入示例数据.
The first thing I created was @Customers TABLE variable to mimic the data structure you showed in your example and inserted your sample data.
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会返回以下内容:
Running a SELECT against @Customers returns the following:
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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"}]}]} |
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
接下来，我匹配了您的更新规则:更新嵌套的JSON值，该值仅限于特定的CustomerId(9ee07040-c001-11e9-b29a-55eb3439cd7c)和feedName(ccsdcdscsdc).
Next, I matched your rules for the update: Update a nested JSON value that is restricted to a specific CustomerId (9ee07040-c001-11e9-b29a-55eb3439cd7c) and a feedName (ccsdcdscsdc).
就像我提到的那样，我们首先需要解压" JSON，因为我们不知道应该更新的特定键(索引)值.完成这两项任务(解包/更新)的最简单方法是使用通用表表达式(CTE).
Like I mentioned, we need to "unpack" the JSON first because we don't know the specific key (index) value that should be updated. The easiest way to accomplish both tasks (unpack/update) is to use a Common Table Expression (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 .
The CTE allows us to "unpack" (I made this word up as it seemed fitting) the JSON contained in configJSON, which then allows us to apply a filter against the feedName.
AND JSON_VALUE( Config.value, '$.feedName' ) = 'ccsdcdscsdc'
您还将注意到，我们包含了CustomerId规则:
You'll also note that we included the CustomerId rule:
Customer.CustomerId = '9ee07040-c001-11e9-b29a-55eb3439cd7c'
CustomerId 和 feedName 都可以很容易地成为SQL变量.
Both the CustomerId and feedName could easily be SQL variables.
那么，这是做什么的?如果要查看Configs_CTE结果集(通过将UPDATE ...更改为SELECT * FROM Config_CTE)，我们将看到:
So, what did this do? If we were to look at Configs_CTE resultset ( by changing the UPDATE... to SELECT * FROM Config_CTE ) we would see:
+--------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+
| 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).
There is a bunch of information here, but what we really care about is the "key" column as this contains the feed index ( in this case 0 ) that we want to update.
这样，就能够完成feedName为"ccsdcdscsdc"的"feed"的请求和UPDATE格式从"Excel"到"MS Excel".
With that, was able to complete the request and UPDATE format from "Excel" to "MS Excel" for the "feed" with the feedName of "ccsdcdscsdc".
这个家伙(注意使用Config_CTE.[key]):
This guy ( note the use of Config_CTE.[key] ):
UPDATE Config_CTE
SET configJSON = JSON_MODIFY( configJSON, '$.feeds[' + Config_CTE.[key] + '].format', 'MS Excel' );
它起作用了吗?让我们看一下更新后的表的数据.
Did it work? Let's look at the updated table's data.
+--------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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美化了"(很确定我没有组成那个).
Here's the updated JSON "beautified" (pretty sure I didn't make up that one).
{
"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 的格式已经从"Excel"更新为"MS Excel".我不清楚您要更新的内容，因此我在测试/示例中使用了格式.
Well, there you have it, format for feedName "ccsdcdscsdc" has been updated from "Excel" to "MS Excel". I was not clear on what you were trying to update, so I used format for my testing/example.
我希望这可以使您朝着正确的方向前进.编码愉快！
I hope this gets you moving in the right direction with your task. Happy coding!
这是可以在SSMS中运行的完整示例:
Here's the complete example that can be run in 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用全新的feed更新feed 饲料
i wanted to update the feed with the given feedId with the whole new feed
要将一个提要"替换为全新的提要，您可以执行以下操作:
To replace one "feed" with an entirely new feed, you may do the following:
-- 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 ) );
运行此后，提要现在显示为:
After running this, the feeds now appear as:
{
"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"
}
]
}
]
}
请注意在UPDATE中使用JSON_QUERY( @MyNewJson ).这很重要.
Note the use of JSON_QUERY( @MyNewJson ) in the UPDATE. This is important.
来自Microsoft的文档:
From Microsoft's Docs:
不带第二个可选参数的JSON_QUERY仅返回 结果是第一个参数.由于JSON_QUERY始终返回有效 JSON，FOR JSON知道不必逃避此结果.
JSON_QUERY without its optional second parameter returns only the first argument as a result. Since JSON_QUERY always returns valid JSON, FOR JSON knows that this result does not have to be escaped.
如果不使用JSON_QUERY传递@MyNewJson，您的新json将被转义(例如"customerName"变为\"customerName \" )，就像将其存储为纯文本一样. JSON_QUERY将返回未转义的有效JSON，这对于您而言是必需的.
If you were to pass @MyNewJson without the JSON_QUERY your new json would be escaped ( e.g., "customerName" becomes \"customerName\" ) as if it were being stored as plain text. JSON_QUERY will return unescaped, valid JSON which is necessary in your case.
还请注意，我对替换整个Feed和单个商品值所做的唯一更改是切换
Also note that the only change I made to replace the entire feed vs. a single item value was switching
'$.feeds[' + Config_CTE.[key] + '].format'
到
'$.feeds[' + Config_CTE.[key] + ']'.
这篇关于如何在MSSQL中更新JSON中的嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
