json_modify 條件

與我之前的問題一樣，我正在將基於 XML 的程序遷移到 JSON 程序。我最新的鬥爭是 json_modify

我習慣於使用 .modify 和 XPATH 來動態修改 XML。

例如，如果我有一個如下所示的 XML（範例取自 SqlShack）

<users>
 <user user_id="1">
   <fname>Josef</fname>
   <lname>Brown</lname>
   <email_>jo0@adventure-works.com</email_>
   <gender>M</gender>
 </user>
 <user user_id="2">
   <fname>Katie</fname>
   <lname>McAskill-White</lname>
   <email_>katie0@adventure-works.com</email_>
 </user>
 <user user_id="2">
   <fname>Maddie</fname>
   <lname>Brown</lname>
   <email_>madhatter@adventure-works.com</email_>
 </user>
</users>

我想將所有 user_id=“2” 的使用者的名稱更改為 “Nat”，我可以編寫以下內容：

xml.modify('replace value of (/users/user[@user_id=("2")]/fname/text()) with "Nat"')

現在讓我們談談 JSON。假設我有以下 JSON

{"Users":
 [{"user_id":1,"fname":"Josef","lname":"Brown","email":"jo0@adventure-works.com","gender":"M"},
  {"user_id":2,"fname":"Katie","lname":"McAskill-White","email":"katie0@adventure-works.com"},
  {"user_id":2,"fname":"Maddie","lname":"Brown","email":"madhatter@adventure-works.com"}]
}

我想做和上面一樣的事情，但是使用這個 JSON。我如何使用 json_modify 來做到這一點？

.modify首先，無論如何，您實際上不能同時修改多個節點。您通常會在循環或遞歸 CTE 中執行此操作，或者通過重新建構 XML 來執行此操作。

使用 JSON 執行此操作要復雜得多，因為 SQL Server 中的 JPath 不支持任何形式的謂詞。對於需要謂詞的每個級別，您都需要將其分開。

假設您只想在第一項上執行此操作。您可以使用JSON_MODIFY，但您需要動態建構 JPath，它僅適用於 SQL Server 2017+。

UPDATE t
SET json = JSON_MODIFY(t.json, '$.Users[' + j.[key] + '].fname', 'Nat')
FROM t
CROSS APPLY (
   SELECT TOP 1 j.[key]
   FROM OPENJSON(t.json, '$.Users') j
   WHERE JSON_VALUE(j.value, '$.user_id') = '2'
) j;

或者對於你可以做的變數

SET @json = JSON_MODIFY(@json, '$.Users[' + (
   SELECT TOP 1 j.[key]
   FROM OPENJSON(@json, '$.Users') j
   WHERE JSON_VALUE(j.value, '$.user_id') = '2'
 ) + '].fname', 'Nat');

db<>小提琴

如您所見，它變得複雜且僅適用於一個值，並且僅適用於 SQL Server 2017+。

一個更好的選擇，特別是如果你有多個值，是重建 JSON。

您可以全力以赴並重建每個對象：

UPDATE t
SET json = j.json
FROM t
CROSS APPLY (
   SELECT
     user_id,
     fname = CASE WHEN user_id = 2 THEN 'Nat' ELSE fname END,
     lname,
     email,
     gender
   FROM OPENJSON(t.json, '$.Users')
     WITH (
       user_id int,
       fname varchar(50),
       lname varchar(50),
       email varchar(255),
       gender char(1)
     ) j
   FOR JSON PATH, ROOT('Users')
) j(json);

db<>小提琴

但是在很多情況下，最簡單的方法是只重建到您需要更改的級別之上，然後使用JSON_MODIFY.

在這種特殊情況下，這意味著您只需要在沒有模式的情況下進行數組拆分OPENJSON，然後有條件JSON_MODIFY地處理對象。SQL Server 沒有JSON_AGG，所以我們需要用 hack 來破解它STRING_AGG：

UPDATE t
SET json = j.json
FROM t
CROSS APPLY (
   SELECT Users = JSON_QUERY('[' + STRING_AGG(CAST(
       CASE WHEN JSON_VALUE(j.value, '$.user_id') = '2'
         THEN JSON_MODIFY(j.value, '$.fname', 'Nat')
         ELSE j.value END
     AS nvarchar(max)), ',') + ']')
   FROM OPENJSON(t.json, '$.Users') j
   FOR JSON PATH
) j(json);

db<>小提琴

引用自：https://dba.stackexchange.com/questions/305580