How to compare two rows of JSON values in SQL?-CodePudding

I have a billing data stored in a table like this in SQL. I want to compare BillContent (Json Value with unknown properties). My goal is to compare current version's BillContent from previous version to see what changes have been made by different departments. As a result, I would like to see the changed value. What would be the easiest way to get this done in SQL?

	ID	BillID	VersionNo	BillContent
1	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":[]}
2	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":[]}
3	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":[]}

First I used this statement to get the table above:

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

The output of a desired solution could be just getting the difference between two Json like a table below:

Possible Output 1: This output returns the json with only properties that are different or changed in comparing version from original version

	Difference
1	{"Description":"testing","LogbillingInfo":false,"ForwardBill":false,"VIP":false}

Possible Output 2: This output returns a VersionNo and each property of a json as a column for all versions of a given process. NOTE: some of the properties have nested arrays.

	VersionNo	ID	Description	Active	AddressID	DepartmentID	LogbillingInfo	ForwardBill	VIP	BillingBranchSectors	BillingMetaData	BillingVersions
1	1	9	testing Bill	true	14	9	true	true	false	[{"SectorID":9,"InspectorID":0,"BillingMovingSteps":[]}]	NULL	NULL
2	2	9	testing Bill	true	14	9	true	true	false	[{"SectorID":9,"InspectorID":0,"BillingMovingSteps":[]}]	NULL	NULL
3	3	9	testing Billagain	true	14	9	true	false	true	[{"SectorID":9,"InspectorID":2,"BillingMovingSteps":[]}]	["metaDatasteps": true, DescriptiveDetail:[]]	NULL

Possible Solution: The simplest way to compare these bill versions is by using SQL built-in JSON_VALUE as suggested by YuTing below. This will return each property of a BillContent (JSON) from version table as a column for all the versions of a given bill id. This is a great solution for the cases where the properties of JSON object are known. However, JSON_VALUE does not work for arrays as we have some properties like 'BillingVersions' in our JOSN containing arrays. That’s where JSON_QUERY comes. The example of this approach for bill id 9 is as follows:

    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

CodePudding user response：

Use json_value, check the document from microsoft.

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

dbfiddle