如何比較SQL中的兩行JSON值？

我有一個計費資料存盤在 SQL 中這樣的表中。我想比較 BillContent（具有未知屬性的 Json 值）。我的目標是將當前版本的 BillContent 與以前的版本進行比較，以了解不同部門進行了哪些更改。因此，我希望看到更改后的值。在 SQL 中完成這項作業的最簡單方法是什么？

首先我用這個陳述句來得到上面的表格：

select bvt.*
from BillVersionTable bvt
where BillID = 9
enter code here

所需解決方案的輸出可能只是得到兩個 Json 之間的差異，如下表所示：

可能的輸出 1：此輸出回傳的 json 僅具有與原始版本比較版本不同或更改的屬性

可能的輸出 2：此輸出回傳 VersionNo 和 json 的每個屬性作為給定行程的所有版本的列。注意：一些屬性有嵌套陣列。

可能的解決方案：比較這些賬單版本的最簡單方法是使用 SQL 內置 JSON_VALUE，如下 YuTing 建議的那樣。這將從版本表中回傳 BillContent (JSON) 的每個屬性作為給定賬單 ID 的所有版本的列。對于 JSON 物件的屬性已知的情況，這是一個很好的解決方案。但是，JSON_VALUE 不適用于陣列，因為我們在包含陣列的 JOSN 中有一些屬性，例如“BillingVersions”。這就是 JSON_QUERY 的來源。票據 id 9 的這種方法的示例如下：

    select bv.VersionNo,
JSON_VALUE(BillContent, '$.ID') AS ID,
JSON_VALUE(BillContent, '$.Description') AS Description,
JSON_VALUE(BillContent, '$.Active') AS Active,
JSON_VALUE(BillContent, '$.AddressID') AS AddressID,
JSON_VALUE(BillContent, '$.DepartmentID') AS DepartmentID,
JSON_VALUE(BillContent, '$.LogbillingInfo') AS LogbillingInfo,
JSON_VALUE(BillContent, '$.CancellingBillingInfo') AS CancellingBillingInfo,
JSON_VALUE(BillContent, '$.ForwardBill') AS ForwardBill,
JSON_VALUE(BillContent, '$.VIP') AS VIP,
JSON_QUERY(BillContent, '$.BillingBranchSectors') AS BillingBranchSectors,
JSON_QUERY(BillContent, '$.BillingVersions') AS BillingVersions,
JSON_QUERY(BillContent, '$.BillingMetaData') AS BillingMetaData
from BillVersionTable bv
where BillID = 9

uj5u.com熱心網友回復：

使用json_value，檢查微軟的檔案。

CREATE TABLE data
(
    ID int NOT NULL,
    BillID int NOT NULL,
    VersionNo int NOT NULL,
    BillContent nvarchar(1000) NOT NULL,
);

insert into data values
 (150, 9, 1, '{"ID":9,"Description":"testing Bill","Active":true,"AddressID":14,"DepartmentID":9,"LogbillingInfo":true,"ForwardBill":true,"VIP":false,"BillingBranchSectors":[{"SectorID":9,"InspectorID":0,"BillingMovingSteps":[]}],"BillingVersions":[]}')
,(151, 9, 2, '{"ID":9,"Description":"testing Bill","Active":true,"AddressID":14,"DepartmentID":9,"LogbillingInfo":true,"ForwardBill":true,"VIP":false,"BillingBranchSectors":[{"SectorID":9,"InspectorID":0,"BillingMovingSteps":[]}],"BillingVersions":[]}')
,(152, 9, 3, '{"ID":9,"Description":"testing Billagain","Active":true,"AddressID":14,"DepartmentID":9,"LogbillingInfo":true,"CancellingBillingInfo":false,"ForwardBill":true,"VIP":false,"BillingBranchSectors":[{"SectorID":9,"InspectorID":0,"BillingMovingSteps":[]}],"BillingMetaData":[],"BillingVersions":[]}')

select 
JSON_VALUE(BillContent, '$.ID') AS ID,
JSON_VALUE(BillContent, '$.Description') AS Description,
JSON_VALUE(BillContent, '$.Active') AS Active,
JSON_VALUE(BillContent, '$.AddressID') AS AddressID,
JSON_VALUE(BillContent, '$.DepartmentID') AS DepartmentID,
JSON_VALUE(BillContent, '$.LogbillingInfo') AS LogbillingInfo,
JSON_VALUE(BillContent, '$.CancellingBillingInfo') AS CancellingBillingInfo,
JSON_VALUE(BillContent, '$.ForwardBill') AS ForwardBill,
JSON_VALUE(BillContent, '$.VIP') AS VIP,
JSON_VALUE(BillContent, '$.BillingBranchSectors') AS BillingBranchSectors,
JSON_VALUE(BillContent, '$.BillingVersions') AS BillingVersions,
JSON_VALUE(BillContent, '$.BillingMetaData') AS BillingMetaData
from data
where BillID = 9

小提琴手

標籤：sql sql服务器

上一篇：存盤程序：回傳一周的前五天

下一篇：如何禁用'MonthCalendar'和Datetimepicker的VB.NET視覺樣式？