Sql-Server
將 json 值更新為 null
據我了解,如果您選擇
json_value
或json_query
指定 json 文件中不存在的鍵或對象,那麼在嚴格模式下,您將收到錯誤消息。例如,這允許您確認是否在文件中指定了密鑰。如果文件中包含範例鍵值對,則:
"Test":null
…這將在嚴格模式下返回“NULL”。換句話說,您現在知道在文件中定義了鍵並且其值為空。
現在想像您的文件包含:
"Test":"Some string"
你查詢:
select json_modify(@json, '$.Test', null);
這將返回一個缺少“Test”鍵的 json 字元串。
您如何正確地將鍵值設置為 null,而不是從 json 文件中刪除它?
(上面的範例可能看起來很荒謬,但我可以想像在將 json 文件傳遞給下一個查詢或系統之前將值設置為 null,並要求文件中存在您的“測試”鍵。)
根據 MS Docs 關於JSON_MODIFY:
+----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+ | Existing value | Path exists | Lax mode | Strict mode | +----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+ | Not NULL | Yes | Update the existing value. | Update the existing value. | +----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+ | Not NULL | No | Try to create a new key:value pair on the specified path.This may fail. | Error - INVALID_PROPERTY | | | | For example, if you specify the path $.user.setting.theme, JSON_MODIFY does not insert | | | | | the key theme if the $.user or $.user.settings objects do not exist, or if settings | | | | | is an array or a scalar value. | | +----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+ | NULL | Yes | Delete the existing property. | Set the existing value to null. | +----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+ | NULL | No | No action. The first argument is returned as the result. | Error - INVALID_PROPERTY | +----------------+-------------+-----------------------------------------------------------------------------------------+---------------------------------+
使用文件提供的範例,只需在 json 路徑前添加關鍵字 strict:
DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'; SET @info=JSON_MODIFY(@info,'strict $.name',NULL): PRINT @info;
返回
{"name":null,"skills":["C#","SQL"]}
db<>在這裡擺弄