我有一個修改一些 JSON 的 SQL 查詢。我正在遍歷資料并根據迭代修改部分 JSON。
為此，我需要將一個可變引數傳遞給 JSON_MODIFY，但由于某種原因，它不起作用！
SET @json = JSON_MODIFY(@ProData, '$.' @ProKey '.hasAnswer', CAST(1 as BIT))
我也嘗試過傳遞一個變數：
DECLARE @hasAnswerPath VARCHAR(100);
SET @hasAnswerPath = '$.' @ProKey '.hasAnswer';
SET @json = JSON_MODIFY(@ProData, @hasAnswerPath, CAST(1 as BIT))
但它具有相同的輸出，hasAnswer 被添加到 JSON 的根，而不是在@ProKey 指定的元素中。
這作業得很好：
SET @json = JSON_MODIFY(@ProData, '$.SomeName1.hasAnswer', CAST(1 as BIT))
這就像忽略了@ProKey。
完整查詢：
BEGIN TRAN
DECLARE @ProID as uniqueidentifier;
DECLARE @ProData as nvarchar(max);
DECLARE @ProKey as varchar(200);
DECLARE ProCursor CURSOR FOR
SELECT Id, [Data] FROM [dbo].[PRO]
OPEN ProCursor;
FETCH NEXT FROM ProCursor INTO @ProID, @ProData;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @json NVARCHAR(max);
DECLARE DataCursor CURSOR FOR
SELECT [key] FROM OPENJSON(@ProData) WHERE type = 5; --5 is object data
OPEN DataCursor;
FETCH NEXT FROM DataCursor INTO @ProKey;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @json=JSON_MODIFY(@ProData, '$.' @ProKey '.hasAnswer', CAST(1 as BIT))
SET @json=JSON_MODIFY(@json,'$.' @ProKey '.questionType','intro')
FETCH NEXT FROM DataCursor INTO @ProKey;
END;
UPDATE [dbo].[PRO]
SET [Data] = @json
WHERE Id = @ProID
PRINT @json
CLOSE DataCursor;
DEALLOCATE DataCursor;
FETCH NEXT FROM ProCursor INTO @ProID, @ProData;
END
CLOSE ProCursor;
DEALLOCATE ProCursor;
ROLLBACK
示例 JSON：
{
"SomeName1": {
"header": "Some text": {
"type": "specified",
"numberValue": 1.0
}
},
"SomeName2": {
"header": "Some text",
"answer": {
"type": "specified",
"numberValue": 4.0
}
},
"SomeName3": {
"header": "Some text",
"answer": {
"type": "specified",
"numberValue": 2.0
}
}
}
預期結果：
},
"SomeName1": {
"header": "Some text",
"answer": {
"type": "specified",
"numberValue": 1.0
}
"hasAnswer": true,
"questionType": "intro",
}
}
實際結果：
},
"SomeName1": {
"header": "Some text",
"answer": {
"type": "specified",
"numberValue": 1.0
}
}
},
"hasAnswer":true,
"questionType":"intro"
}
我在這里做錯了嗎？
解決了：
我把它從：
SET json=JSON_MODIFY(ProData, '$.' ProKey '.hasAnswer', CAST(1 as BIT))
SET json=JSON_MODIFY(json,'$.' ProKey '.questionType','intro')
到：
SET @json = @ProData;
SET json=JSON_MODIFY(json, '$.' ProKey '.hasAnswer', CAST(1 as BIT))
SET @json=JSON_MODIFY(json,'$.' ProKey '.questionType','intro')
原來，資料被覆寫了！
uj5u.com熱心網友回復：
您似乎無意中覆寫了變數：
-- first iteration
SET @json=JSON_MODIFY(@ProData, '$.' @ProKey '.hasAnswer', CAST(1 as BIT))
-- @json contains the @prodata after modification to first key (@prodata itself is not changed)
SET @json=JSON_MODIFY(@json,'$.' @ProKey '.questionType','intro')
-- @json (its first key to be more precise) is modified further
但是在下一次迭代中，此行會將修改@json后的值恢復為 的原始值@prodata。只有最后一個鍵會保留修改：
-- second iteration
SET @json=JSON_MODIFY(@ProData, '$.' @ProKey '.hasAnswer', CAST(1 as BIT))
-- you just overwrote your modifications with the value inside @prodata
解決辦法是稍微重新整理一下代碼，可能會@json在回圈外初始化。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/376084.html