T-SQL:使用JSON_MODIFY通过过滤器修改键值对

Question

我在一个表中有一个JSON列，看起来像这样:

{
"Id": 123 "Filters": [{
"FilterType": "Category",
"Values": {
"23098": "Power Tools",
"12345": "Groceries"
}
}, {
"FilterType": "Distributor",
"Values": {
"98731": "Acme Distribution",
"12345": "Happy Star Supplies"
}
}
]
}

注意"12345"是故意在那里出现两次的，因为查找分发者可能与查找类别不同，所以它不是真正的重复，这很重要。

我将这个JSON数据存储在一个列中，并且用户已重命名为"Groceries"到"食品"。所以它仍然是12345，但现在我想在每个JSON字段中搜索并将杂货替换为食品，其中FilterType为Category, ID为12345。

我已经知道如何找到有它的数据，但是JSON_MODIFY更新语句把我弄混了，因为

1. 我不知道如何在JSON_MODIFY
中使用的json路径中引用特定的键/值对
2. 当我这样做时，我仍然必须指定修改应该只发生在与"Category"相同块中的值12345，而不是分销商。

谁能帮我构造T-SQL魔术，这将是聪明到足以取代适当的键值对(s)在这?(还可以有第三个过滤器，类别类型也有12345:杂货。为了简洁起见，我省略了它，但是您必须假设每个FilterType "Category"中可能有N个不同的过滤器。and Key = 12345.

Answer 1

不能使用JsonPath谓词，路径必须是精确路径

但是在较新版本的SQL Server中，您可以从计算列中提供路径。

UPDATE t
SET json = JSON_MODIFY(t.json COLLATE Latin1_General_BIN2, j.path, 'Food stuffs')
FROM YourTable t
CROSS APPLY (
SELECT TOP (1)
path = CONCAT('$.Filters[', f.[key], '].Values."', v.[key], '"')
FROM OPENJSON(t.json, '$.Filters') f
CROSS APPLY OPENJSON(f.value, '$.Values') v
WHERE JSON_VALUE(f.value, '$.FilterType') = 'Category'
AND v.[key] = '12345'
) j;

,db&lt的在小提琴

---

如果你在每一行的JSON值中有多个更新，那么它会变得更复杂，使用FOR JSON重建JSON通常更容易。

Answer 2

所以我终于让它按照我想要的方式工作了。因为上面的答案说JSON_MODIFY只能使用精确的路径，而且只能在第一次出现时使用，所以我重新运行它，直到@@ROWCOUNT = 0(或迭代的最大次数)。

我做了一个隐式的OPENJSON(获得一个键，它不带显式的OPENJSON)，并使用显式的OPENJSON进一步解构JSON。然后，我可以使用Key引用精确的筛选器组，并使用传入的值作为路径的一部分，为JSON_MODIFY查找正确的键值对。

@TypeID是我想要在我选择的上下文中更改的键值对的int值。

从性能的角度来看，这实际上是相当快的，在我们的Azure托管的SQL Server实例上运行，在一秒钟内找到并替换158个不同的JSON。我可以接受。

Update defs
set defs.data = JSON_MODIFY(defs.data, '$.Filters[' + ImplicitFilters.[Key] + '].Values."'+CAST(@typeId as varchar)+'"', t.[Type] )
from MyTable defs
CROSS APPLY OPENJSON (data , '$.Filters') as ImplicitFilters
CROSS APPLY OPENJSON (ImplicitFilters.Value, '$') 
WITH (FilterType varchar(50) '$.FilterType', Pairs nVarchar(Max) '$.Values' AS JSON) As Filters
CROSS APPLY OPENJSON( Filters.[Pairs] ) as KeyValuePairs
JOIN dbo.[TypesLookup] t on t.typeid = KeyValuePairs.[key]
where    Filters.CriteriaType = 'Category' 
and KeyValuePairs.[Key] = @TypeId 
and KeyValuePairs.[Value] <> t.[name]  -- only change it if it's wrong.