通過導航 SQL Server 中的所有表，從 json 數組中刪除具有給定 id 的元素

哈夫薩國民

所有表都有一個名為BranchList. BranchList是一個包含 json 數組的字符串，像這樣

[ { "Id": 1, "Branch": 259 }, { "Id": 1, "Branch": 253 } ]

我想通過導航所有表從列表中刪除分支 253 的 json 值，它應該是這樣的

[ { "Id": 1, "Branch": 259 } ]

我為這樣的單個表找到了一種方法，但對我來說更新所有表還不夠。

declare @JSONData nvarchar(max)
declare @Id nvarchar(max)
declare @BranchList nvarchar(max)
declare @Counter INT

select @Counter = count(BranchList) 
from WarehouseTypes 
outer apply OPENJSON(BranchList) as s
where JSON_VALUE(s.value,'$.Branch') = 253;

while (@Counter > 0)
begin
    select @Id = Id, @BranchList = BranchList 
    from WarehouseTypes 
    outer apply OPENJSON(BranchList) as s
    where JSON_VALUE(s.value,'$.Branch') = 253;
   
    set @JSONData = N''+@BranchList+'';
    set @JSONData = (select * 
                     from OPENJSON(@JSONData, '$') 
                     with (
                           Id int '$.Id',
                           Branch int '$.Branch'
                          )
                     where not (Branch = 253) 
                     for json path);
   
    exec('update WarehouseTypes set BranchList ='''+@JSONData+''' where Id='+@Id);
    set @Counter = @Counter  - 1
end

預先感謝您的幫助。

若羅夫

您可以嘗試生成並執行動態語句。重要的步驟是：

獲取包含列的每個表的名稱BranchList（使用系統目錄視圖）。
從存儲的 JSON 數組中刪除項目。使用當前版本的 SQL Server，您無法從 JSON 數組中刪除項目，因此您需要將 JSON 數組解析為表（使用OPENJSON()和顯式架構），過濾行並將表的內容輸出為 JSON（使用FOR JSON AUTO）。

T-SQL：

DECLARE @stm nvarchar(max)
DECLARE @err int

SELECT @stm = STRING_AGG(
   CONCAT(
      N'UPDATE ',
      QUOTENAME(sch.[name]), '.',
      QUOTENAME(tab.[name]),
      N' SET ', 
      QUOTENAME(col.[name]), ' = (',
      N'SELECT Id, Branch FROM OPENJSON(',
      QUOTENAME(col.[name]),
      N', ''$'') WITH (Id int ''$.Id'', Branch int ''$.Branch'') WHERE Branch <> 253 FOR JSON PATH',
      N')'
   ),
   '; '
)
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
WHERE col.[name] = 'BranchList' 

PRINT @stm
EXEC @err = sp_executesql @stm
IF @err <> 0 PRINT 'Error found'

結果是一個複雜的語句，包含UPDATE每個表的語句：

UPDATE [dbo].[xxxx] 
SET [BranchList] = (
   SELECT Id, Branch 
   FROM OPENJSON([BranchList], '$') WITH (Id int '$.Id', Branch int '$.Branch') 
   WHERE Branch <> 253 
   FOR JSON AUTO
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。